Oracle SQL | How to select a substring where it starts with a certain letter and ends with a certain symbol?

advertisements

If I had this:

NAME        EYES==ID==HAIR
Jon         Brown==F9182==Red
May         Blue==F10100==Brown
Bill        Hazel/Green==F123==Brown

...and I wanted to create a new ID column with the ID alone, and I know that everyone's ID starts with an 'F' and will end at the '=' how would I select a substring from the compact column and take JUST the ID out?

ex. I want this as the end product

NAME        EYES==ID==HAIR               ID
Jon         Brown==F9182==Red            F9182
May         Blue==F10100==Brown          F10100
Bill        Hazel/Green==F123==Brown     F123

If I can't make it end at '=' is there any way to trim the rest of the content that isn't part of the ID after selecting it?


You can use a Regular Expression:

regexp_substr('Hazel/Green==F123==Brown','(==F.+?==)')

extracts '==F123==', now trim the =:

ltrim(rtrim(regexp_substr('Hazel/Green==F123==Brown','(==F.+?==)'), '='), '=')

If Oracle supported lookahead/lookbehind this would be easier...

Edit:

Base on @ErkanHaspulat's query you don't need LTRIM/RTRIM as you can specify to return only the first capture group (I always forget about that). But just to be safe you should change the regex not to be greedy:

regexp_substr('Hazel/Green==F123==Brown==iii','==(.+?)==', 1, 1, null, 1)