CODEWITHSUNDEEP
sql-serverregex
jallad
jallad

Reputation: 25

Use regex in SQL Server 2014

Can I use regex in SQL Server 2014? For example I have a column mobilenumber contains 7 rows with 8 digit numbers, like 66658697, 25365869 and so on.

How can I find all rows that contains n times of 6 for example?

Upvotes: 0

Views: 1263

Answers (4)

Alex K.
Alex K.

Reputation: 175766

Find 4 or more 6:

select * from t where f like '%6%6%6%6%'

Find exactly 4 x 6: 

select * from t where len(replace(f, '6', '')) = len(f) - 4

Upvotes: 4

John Cappelletti
John Cappelletti

Reputation: 81950

Declare @YourTable table (ID int,mobilenumber varchar(25))
Insert Into @YourTable values
(1,'66658697'),
(2,'25365869')

Select *
 From @YourTable
 Where Len(mobilenumber)-Len(Replace(mobilenumber,'6',''))=2

Returns

ID  mobilenumber
2   25365869

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

If you want numbers that contain 3 sixes, you can use like:

where mobilenumber like '%6%6%6%'

If you want them together, the remove the %s between the sixes.

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

If you don't have a complex pattern to search for, then you can use LIKE:

SELECT *
FROM TABLE
WHERE mobilenumber LIKE '%666%'

Although, if you have a more complicated pattern you want to search for then you can take a look at PATINDEX.

Upvotes: 1

Related Questions