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?
-
1NB: There is no REGEX in SQL (unless you define CLR functions). However, PATINDEX does something similar...JohnLBevan– JohnLBevan2016-12-20 13:18:50 +00:00Commented Dec 20, 2016 at 13:18
Add a comment
|
4 Answers
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
Comments
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.
Comments
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.
Comments
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
