REGEX_REPLACE

Replaces all occurrences of a specified string value that match a java-style regular expression regex with the replacement value. This function is used in search operations for sophisticated pattern matching including repetition and alternation. For more information on the Java format for regular expressions see the java documentation.

Syntax

REGEX_REPLACE ( 'string' , 'regex' , 'replacement' )

Arguments

‘string’

Is the string to search for strings matching the regular expression. ‘string’ can be of a character data type.

‘regex’

Is the regular expression to match. ‘regex’ can be of a character data type.

‘replacement’

Is a string to replace with. ‘replacement’ can be of a character data type.

Return types

Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REGEX_REPLACE returns varchar.

Returns NULL if any one of the arguments is NULL.

If string_input is not of type varchar(max) or nvarchar(max), REGEX_REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_input must be explicitly cast to a large-value data type.

Examples

SELECT id, val, REGEXP_REPLACE(val, '\,', '.') as val_new
FROM
(
   SELECT 1 as id, '1,4567' AS val
   UNION ALL
   SELECT 2, '-0,12345'
) AS dat

See Also

• Functions