All Forums Extensibility
Claradata 8 posts Joined 05/12
16 Jul 2012
Fast Path UDF -- REGEXP_REPLACE not working with Slashes

Database: 14.0

Situation: REGEXP_REPLACE function is not accepting slashes (\) in a regular expression string and replace with empty spaces. I checked the manuals and it is no where listed that we can't search for slashes as regular expression and replace with another character or empty spaces.

Example 1 works when I try to replace 's' with '$'. Example 2 does not work when I try to replace '\' with ' '.

 

Please advise if slashes are not allowed or is there are different way of calling this...

 

Example 1:

 

SELECT REGEXP_REPLACE('standardaddr', 's', '$', 1, 1, 'c');

-- Returns $tandardaddr

 

Example 2:

 

SELECT REGEXP_REPLACE('standard\addr', '\', ' ', 1, 1, 'c');

-- Errors out as invalid pattern (SELECT Failed. 9134:  The pattern specified is not a valid pattern.)

 

 

Jim Chapman 449 posts Joined 09/04
16 Jul 2012

Since "\" is a regular expression metacharacter, you must write "\\" when you want to use it as a literal character.

SELECT REGEXP_REPLACE('standard\addr', '\\', ' ', 1, 1, 'c');

-- returns 'standard addr'

 

Claradata 8 posts Joined 05/12
17 Jul 2012

Thank You Jim...

 

You must sign in to leave a comment.