What would be the equivalent to LIKE '%[^a-z]ACCESS[^a-z]%' in snowflake?-CodePudding

I'm trying to get an output where it has the string access in it and has space on either side. Using ILIKE '% access %' isn't working as I'm getting result with 'accessibility in it.

Tried ILIKE '% access %'.

CodePudding user response：

Try this:

WITH
s(s) AS (
 SELECT 'any ACCESS any'
)
SELECT
  s
, REGEXP_LIKE(s,'^.* ACCESS .*$') AS is_rx_like
FROM s
-- out        s        | is_rx_like
-- out ---------------- ------------
-- out  any ACCESS any | true

CodePudding user response：

An equivalent to LIKE '%[^a-z]ACCESS[^a-z]%' in Snowflake would be using the

You could try using \b which means word boundary, along with 'i' parameter for case insensitive. This gracefully handles the beginning/end of text.

You can tinker with ACCESS vs access and adding/removing the 'i' parameter.

WITH CTE(COL) AS ( SELECT 'any ACCESS any' union all select 'Access blah blah'union all select ' blah blah Access' union all select 'Access'
            union all   SELECT 'any apple any' union all select 'apple blah blah'union all select ' blah blah apple' union all select 'apple' union all select 'accessibility')
SELECT
  COL
, REGEXP_LIKE(COL,'^.* ACCESS .*$' )        MARCOTHESANE
, REGEXP_LIKE(TRIM(COL),'.* ACCESS .*')     BITCOUB
, REGEXP_LIKE(COL,'.*(\\bACCESS\\b).*','i')    ALT_ANSWER
FROM CTE;