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:
extracts '==F123==', now trim the
ltrim(rtrim(regexp_substr('Hazel/Green==F123==Brown','(==F.+?==)'), '='), '=')
If Oracle supported lookahead/lookbehind this would be easier...
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)