How to filter based on a word in a sentence with special characters regex snowflake?-CodePudding

I have a string that looks like

***z|Alphanumeric | Jhon Jones| PA | String |Date:73/65/86-58/82/03,44/79/11-26/33/60|Date:Not Valid | Arbitrary Fees|Amount:11111|Frequency:Monthly |

How do I ignore everything and just fetch rows based on "Arbitrary Fees"? I tried the below query and it doesn't work

select 
MSTR_APLN_ID,
CMNT_LN_1_TXT_TOK
from SFAAP.V_SOT_DIRBNK_CLB_DPL_MRD.apln_cmnt_hist 
where lower(CMNT_LN_1_TXT_TOK) Rlike '.* Arbitrary Fees.*'

CodePudding user response：

Greg's answer is correct and I'm a big fan of REGEX functions, but your query doesn't seem to use the power of regex. Why don't you use LIKE or ILIKE?

Case-insensitive:

select 
MSTR_APLN_ID,
CMNT_LN_1_TXT_TOK
from SFAAP.V_SOT_DIRBNK_CLB_DPL_MRD.apln_cmnt_hist 
where CMNT_LN_1_TXT_TOK ILIKE '%Arbitrary Fees%'

Case-sensitive:

select 
MSTR_APLN_ID,
CMNT_LN_1_TXT_TOK
from SFAAP.V_SOT_DIRBNK_CLB_DPL_MRD.apln_cmnt_hist 
where CMNT_LN_1_TXT_TOK LIKE '%Arbitrary Fees%'

CodePudding user response：

You have the column's string wrapped in the lower function, but your string has two capital letters:

lower(CMNT_LN_1_TXT_TOK) Rlike '.* Arbitrary Fees.*'

You can change to:

 CMNT_LN_1_TXT_TOK Rlike '.*Arbitrary Fees.*'

If you want to match any case for the search term:

lower(CMNT_LN_1_TXT_TOK) Rlike '.*arbitrary fees.*'