SohamC SohamC - 3 months ago 8
SQL Question

Regex pattern inside REPLACE function

SELECT REPLACE('ABCTemplate1', 'Template\d+', '');
SELECT REPLACE('ABC_XYZTemplate21', 'Template\d+', '');


I am trying to remove the part
Template
followed by n digits from a string. The result should be

ABC
ABC_XYZ


However REPLACE is not able to read regex. I am using SQLSERVER 2008. Am I doing something wrong here? Any suggestions?

vkp vkp
Answer
SELECT SUBSTRING('ABCTemplate1', 1, CHARINDEX('Template','ABCTemplate1')-1)

or

SELECT SUBSTRING('ABC_XYZTemplate21',1,PATINDEX('%Template[0-9]%','ABC_XYZTemplate21')-1)

More generally,

SELECT SUBSTRING(column_name,1,PATINDEX('%Template[0-9]%',column_name)-1)
FROM sometable
WHERE PATINDEX('%Template[0-9]%',column_name) > 0

You can use substring with charindex or patindex if the pattern being looked for is fixed.