Regex word-boundary not working as expected

Question

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?

Answer 1

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).