Snowflake SQL LIKE ESCAPE with underscores and wildcards for one symbol-CodePudding

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?

CodePudding user response：

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_part(CD, '_', 3) as LANGUAGE_CODE
from T1 where len(LANGUAGE_CODE) = 3;