I need to return a language code substring from long codes which have specifically 3 letters after second underscore.
The codes in the table are like these:
SA_ROWERP0129_ITA_GECP_AUD_ENG_QUATRO
SA_COWER0123_ITA_LECP_AUD_SPA_QUATRO
SA_ZAEP0127_WCPE_2_AUD_ENG_QUATRO
So I need to return 1st and 2nd rows (Third should not be returned it has 4 letters WCPE)
I was trying LIKE and 6 underscores with ESCAPE:
CASE WHEN code LIKE '%^_%^_%^_%^_%^_%^_QUATRO' ESCAPE '^' --this affects all 3
So I tried adding "unescaped" underscores for the three letters:
CASE WHEN code LIKE '%^____^_%^_%^_%^_%^_QUATRO' ESCAPE '^' --this did not work
How can I combine underscores (wildcards for 1 symbol) with escaped underscores (actual underscore symbol) to affect only codes 1 and 2?
Should I use RLIKE with some regex instead?
>Solution :
You can use regexp functions, or the split function to grab the third group of characters and check its length:
create or replace table T1(CD string);
insert into T1 (CD) values
('SA_ROWERP0129_ITA_GECP_AUD_ENG_QUATRO'),
('SA_COWER0123_ITA_LECP_AUD_SPA_QUATRO'),
('SA_ZAEP0127_WCPE_2_AUD_ENG_QUATRO');
select *, split(CD, '_')[2]::string as LANGUAGE_CODE
from T1 where len(LANGUAGE_CODE) = 3;