sqlregexregex-lookaroundsprestotrino

Presto SQL-Extract sting from the last occurence of char (:) from the right


My goal is to get all the substrings after the last occurene of the char hypen from the right in each string separated by hypen. Ive tried, but i'm getting wrong values if there are multiple "Hypen" encountered from the string. Is there any workaround or approach. Thank you in Advance.

sample input and result:

my code: substring(con.itemid,INSTR(con.itemid,'-')+1) as itemid_suffix,

enter image description here

Another code but getting wrong result: select SUBSTR(ltrim(regexp_extract('AAAA-BBBB-PUB','-([^:]*)',1)),1,2)


Solution

  • Based on the provided data you can use simple pattern of any non-hyphen characters and end of the string - [^-]*$:

    -- sample data
    with dataset(str) as(
        VALUES ('aaa-bbb'),
            ('aaa-bbb-ccc'),
            ('111-112') 
    ) 
    
    -- query
    SELECT regexp_extract(str, '[^-]*$')
    FROM dataset;
    

    Output:

     _col0
    -------
     bbb
     ccc
     112