SQL Query to select a string between two characters

Question

I want to retrieve a String between two characters. I have whole string like "Attachments:Attachments~Attachment" and I want to take substring between : and ~ characters that is output will be Attachments. How can be this done in SQL/Oracle select statement?

Answer 1

You can use the REGEXP_SUBSTR function. Starting with Oracle 11g, there is a parameter to the function, which lets you specify the capture group that you want returned:

SELECT regexp_substr('Attachments:Attachments~Attachment', '\:(.+)\~', 1,1,NULL,1) from dual;

There are workarounds for older versions (see also https://stackoverflow.com/a/7759146/14015737). You can shorten your result:

SELECT rtrim(ltrim(regexp_substr('Attachments:Attachments~Attachment', '\:(.+)\~'),':'), '~') FROM dual;

or

SELECT substr( match, 2, length(match)-2 ) from (
SELECT regexp_substr('Attachments:Attachments~Attachment', '\:(.+)\~') match FROM dual);