T-SQL regular expression replace

Question

I need to do a replacement of the string in SQL server. I know that t-sql does not completely support this regex replace feature, but we can use functions such as PATINDEX to do that.

Basically, what I need is to replace a string of URL start with www or www[0-9], for example:

• www.123456.com => 123456.com
• www2.123456.com => 123456.com

I tried: PATINDEX('(www[0-9]?)\.%',@url), but it does not match what I need. Does anyone know how to simply do this without a function CLR ?

Answer 1

You can

with t(f) as (
    select 'www.foo.com' union
    select 'www9.bar.com' union
    select 'wwwz.quz.com' union
    select 'mail.xxx.com'
)
select 
    case when patindex('www.%', f) + patindex('www[0-9].%', f) = 0 then f else substring(f, 1 + charindex('.', f), len(f)) end
from t

For

(No column name)
mail.xxx.com
foo.com
bar.com
wwwz.quz.com