Using regular expression in Ms Sql Server

Report · Jul 07, 2011

I have a problem regarding sql query like below.

Table Name:Distributors

Colname:Phone varchar(20)

Data for Phone No:(965)-122-4526 or 965-122-4526.

I want to perform a select operation in Sql server(2008) using regular expression like below.

Select * from Distributors

Where

REPLACE(Phone,'regexp','') = <cfqueryparam cfsqltype="cf_sql_varchar" value="#rereplacenocase(trim(arguments.phone),'[\s\)\(-]','','all')#">

The above query is written inside a function and the value of "arguments.phone" may be (965)-122-4526 or 965-122-4526 or 9651224526

The problem is i am not getting the regular expression which will remove "(" , ")" , "space" , "-" in MSSQL.

can anybody help me?

Report · Jul 07, 2011

OK, so you're asking a very very SQL-server-specific question on a ColdFusion forum...?

I'm RTFMing the T/SQL docs (crazy idea) and it doesn't seem to me like replace() supports regexes:

http://msdn.microsoft.com/en-us/library/ms186862.aspx

You might want to read the T/SQL docs and see how regexes are implemented in SQL Server 2008. Last time I checked, SQL Server didn't really support 'em at all, although this was a coupla years back.

I just searched on MSDN and got no matches for "regular expression" for SQL Server.

But best you ask SQL Server questions on a forum appropriate to that topic. You'll get more informed answers.

--

Adam

Report · Jul 07, 2011

Data for Phone No:(965)-122-4526 or 965-122-4526.

This is a better question for a sql server forum, but I have to ask ... why store formatted values? I usually store the unformatted value and do any formatting on the front end. Or if that is not feasible, I create a separate column for storing the unformatted values. Then use the second column for searching.

Last time I checked, SQL Server didn't really support 'em at all, although this was a coupla years back.

Yep. Traditionally it never had a great range of string functions. From what I remember true regex support required writing a CLR. Other than that you were reduced to using while loops or a crazy xml path hack I never quite understood 😉