Skip to main content
glamorous_Wonder6C1C
glamorous_Wonder6C1C
Inspiring
July 7, 2011
Question

Using regular expression in Ms Sql Server

  • July 7, 2011
  • 1 reply
  • 2643 views

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?

This topic has been closed for replies.

1 reply

A
Adam Cameron.
Inspiring
July 7, 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

    -
    -__cfSearching__-
    Inspiring
    July 7, 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 ;-)

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices