Selecting a substring from a column with another string as a reference

[chris01010]

Hi,

I'm trying to select a substring within a column but using another string as the starting point. This is because the position of the string in the column may change.

i.e.

select substr(<column name>,<reference string>,3) from table;

ABCDEFGHIJKLMNOPMSGNO123ABCDEFGHIJKL

The refence string in the above is MSGNO so the query would return 123.

Question 1: Is this even possible?

Question 2: How do you do it? :0)

Thanks

Chris

 

[cmmrfrds]

SELECT
substr('ABCDEFGHIJKLMNOPMSGNO123ABCDEFGHIJKL',
(instr('ABCDEFGHIJKLMNOPMSGNO123ABCDEFGHIJKL','MSGNO')+5),3) as x,
REGEXP_SUBSTR('ABCDEFGHIJKLMNOPMSGNO123ABCDEFGHIJKL', '[0-9]+') as z
FROM dual

Another way is to use regular expressions which is a good way to find patterns in a string.

 

[chris01010]

Thanks, exactly what I needed.