sqlregexsnowflake-cloud-data-platformword-boundary

Regex word-boundary not working as expected

I'm trying replace CT with COURT regardless of where it appears in a string (using Snowflake SQL). I would expect this to work:

select
    regexp_replace('36 HERITAGE CT', '\bCT\b', 'COURT'),
    regexp_replace('36 HERITAGE CT #204', '\bCT\b', 'COURT')

But the output is always 36 HERITAGE CT no matter what I do.

Anyone know what I'm doing wrong?

Solution

  • As per documentation, \b is supported.

    Use

    regexp_replace('36 HERITAGE CT', '\\bCT\\b', 'COURT')

    See Note:

    In single-quoted string constants, you must escape the backslash character in the backslash-sequence. For example, to specify \d, use \\d. For details, see Specifying Regular Expressions in Single-Quoted String Constants (in this topic).

    You do not need to escape backslashes if you are delimiting the string with pairs of dollar signs ($$) (rather than single quotes).