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