Geek Answers Handbook

T-SQL regex replaces

I need to perform a string replacement on a SQL server. I know that t-sql does not fully support this regular expression replacement function, but for this we can use functions such as PATINDEX .

Basically, I need to replace the URL string 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 that doesn't match me. Does anyone know how easy it is to do this without the CLR function?

+6
source share
2 answers

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
+8
source

You can use the functions Stuff() and CharIndex() :

 Select stuff(url ,1, charindex('.', url), '') newUrl From t

Demo Screenshot 1

According to the comments, you can perform another check using the CASE expression:

 select case when charindex('.', url, charindex('.', url)+ 1) > 0 then stuff(url ,1,charindex('.', url),'') else url end newUrl from t

Demo Screenshot 2

Or, if you need to check the first 3 characters for www;

 select case when left(url,3) = 'www' then stuff(url ,1,charindex('.', url),'') else url end newUrl from t

Demo Screenshot 3

+2
source

More articles:


All Articles