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,
Another code but getting wrong result: select SUBSTR(ltrim(regexp_extract('AAAA-BBBB-PUB','-([^:]*)',1)),1,2)
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