SQL Oracle | How would I select a substring where it begins with a certain letter and ends with a certain symbol?

Question

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?

Answer 1

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)

Answer 2

    UPDATE table 
    set ID = substr(EYES==ID==HAIR,
INSTR(EYES==ID==HAIR,'=',1,2)+1,
(INSTR(EYES==ID==HAIR,'=',1,3)-INSTR(EYES==ID==HAIR,'=',1,2)-1))
    where primary_key = primary_key;

to make it easy... replace primary_key to the column you have set as primary key and table as your table name.

one of the easiest way for learners to extract words in between is using INSTR() and SUBSTR().

Answer 3

The idea is to find the portion of the string you want using a regular expression. In the query below, notice the () characters that defines a sub-expression in the given regular expression. That is the magic part; you can use as many sub-expressions you want in a regular expression and select them using the final parameter of regexp_substr function.

See documentation for details.

with sample_data as(
    select 
    'Brown==F9182==Red' text
    from dual)
select
    text
    ,regexp_substr(text, '==(.+)==', 1, 1, null, 1)
from sample_data

Answer 4

Use REGEXP_SUBSTR and a combination of LTRIM & RTRIM

SELECT name, EYESIDHAIR, LTRIM(RTRIM(REGEXP_SUBSTR(EYESIDHAIR,'(==F.+?==)'), '='),'=') AS ID
FROM yourtable

OUTPUT:

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

SQL Fiddle: http://sqlfiddle.com/#!4/ebc01/17/0