Regex pattern inside REPLACE function

Question

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?

Possible duplicate of [Regex pattern inside SQL Replace function?](http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function) — Siyual, Sep 07 '16 at 13:37
Yes @alex. Everything including the word `Template` and whatever succeeds after that(its only going to be digits). — SohamC, Sep 07 '16 at 13:42

score 1 · Accepted Answer · answered Sep 07 '16 at 13:42

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.

score 1 · Answer 2 · answered Sep 07 '16 at 13:43

1

select SUBSTRING('ABCTemplate1',1, CHARINDEX ( 'Template' ,'ABCTemplate1')-1)

answered Sep 07 '16 at 13:43

ɐlǝx

1,384
2
17
22

score 1 · Answer 3 · answered Sep 07 '16 at 13:53

1

My answer expects that "Template" is enough to determine where to cut the string:

select LEFT('ABCTemplate1', CHARINDEX('Template', 'ABCTemplate1') - 1)

answered Sep 07 '16 at 13:53

Frog

124
6

TheGameiswar · Answer 4 · 2016-09-07T13:55:26.737

Using numbers table..

;with cte
as
(select 'ABCTemplate1' as string--this can simulate your table column
)
select * from cte  c
cross apply
(
select replace('ABCTemplate1','template'+cast(n as varchar(2)),'') as rplcd
from
numbers
where n<=9
)
b
where c.string<>b.rplcd

Using Recursive CTE..

;with cte
as
(
select cast(replace('ABCTemplate21','template','') as varchar(100)) as string,0 as num
union all
select cast(replace(string,cast(num as varchar(2)),'') as varchar(100)),num+1
from cte
where num<=9
)
select top 1 string from cte
order by num desc

Regex pattern inside REPLACE function

4 Answers4