CODEWITHSUNDEEP
sqlsql-serverregexsql-server-2008
yogi
yogi

Reputation: 19591

SQL Server Regex Select

I have a table that contains Phone# like this:

Phone#
------------------
1234567890
2548796584
9999
2126543133
9999999999
999999
999
9853
9999999

Now I want a query that will return rows having only number 9 in it, in this case result should be.

Phone#
------------------
9999
9999999999
999999
999
9999999

I have heard about regex in Oracle but not in Sql Server. I'm using SQL server 2008 R2, can anyone help me please, thanks in advance.

Upvotes: 0

Views: 720

Answers (4)

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

SQL Server has limited regex functionality, but you can add it using common language runtime procedures. You can get what you're looking for with the code below.

declare @table table (
  [phone] [sysname]);
insert into @table
        ([phone])
values      (N'1234567890'),
        (N'2548796584'),
        (N'9999'),
        (N'2126543133'),
        (N'9999999999'),
        (N'999999'),
        (N'999'),
        (N'9853'),
        (N'9999999');
select [phone] from   @table
where  patindex('%[0-8]%', [phone]) = 0;

Upvotes: 0

Kiran Hegde
Kiran Hegde

Reputation: 3681

You can try this query. 

SELECT  * FROM Phones
WHERE Number NOT LIKE '%[^9]%'

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

Here is a much simpler way than using CLR for this. The reason this like pattern works is we are finding rows that not like NOT 9. The carrot ( ^ ) in RegEx means NOT.

create table #Phones
(
    Num varchar(20)
)

insert #Phones
select '1234567890' union all
select '2548796584' union all
select '9999' union all
select '2126543133' union all
select '9999999999' union all
select '999999' union all
select '999' union all
select '9853' union all
select '9999999' union all
select '12345'

select *
from #Phones
where Num not like '%[^9]%'

Upvotes: 4

Federico Piazza
Federico Piazza

Reputation: 30995

You can use a query like this:

select * from Phones
where 1 = dbo.RegExpLike(number, '^9+$')

You can check more examples about using regex here:

http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server

Upvotes: 0

Related Questions