Regular expressions inside SQL Server
Solution 1
stored value in DB is: 5XXXXXX [where x can be any digit]
You don't mention data types - if numeric, you'll likely have to use CAST/CONVERT to change the data type to [n]varchar.
Use:
WHERE CHARINDEX(column, '5') = 1
AND CHARINDEX(column, '.') = 0 --to stop decimals if needed
AND ISNUMERIC(column) = 1
References:
i have also different cases like XXXX7XX for example, so it has to be generic.
Use:
WHERE PATINDEX('%7%', column) = 5
AND CHARINDEX(column, '.') = 0 --to stop decimals if needed
AND ISNUMERIC(column) = 1
References:
Regex Support
SQL Server 2000+ supports regex, but the catch is you have to create the UDF function in CLR before you have the ability. There are numerous articles providing example code if you google them. Once you have that in place, you can use:
-
5\d{6}
for your first example -
\d{4}7\d{2}
for your second example
For more info on regular expressions, I highly recommend this website.
Solution 2
You can write queries like this in SQL Server:
--each [0-9] matches a single digit, this would match 5xx
SELECT * FROM YourTable WHERE SomeField LIKE '5[0-9][0-9]'
Solution 3
Try this
select * from mytable
where p1 not like '%[^0-9]%' and substring(p1,1,1)='5'
Of course, you'll need to adjust the substring value, but the rest should work...
Solution 4
In order to match a digit, you can use [0-9]
.
So you could use 5[0-9][0-9][0-9][0-9][0-9][0-9]
and [0-9][0-9][0-9][0-9]7[0-9][0-9][0-9]
. I do this a lot for zip codes.
Jack
Updated on July 30, 2022Comments
-
Jack almost 2 years
I have stored values in my database that look like
5XXXXXX
, whereX
can be any digit. In other words, I need to match incoming SQL query strings like5349878
.Does anyone have an idea how to do it?
I have different cases like
XXXX7XX
for example, so it has to be generic. I don't care about representing the pattern in a different way inside the SQL Server.I'm working with c# in .NET.
-
Jack over 14 yearsthanks for reple! these queries will give me many unwanted matches. %7% will match also 3373 which is not a good match for me. 56655% will match also 56655444333 which is again not a good match for me. the query itself has to be with a fixed number. and the DB itself has to have the pattern. is there anything like that?
-
Jack over 14 yearsi haven't completely checked it yet, but i looks like a very good answer. i think that you help me a lot. thank you very much!
-
Sparky over 14 yearsOne minor detail, IsNumeric would consider a decimal point valid, so an expresssion of 5123.45 would pass the IsNumeric () check, but might not meet your criteria
-
OMG Ponies over 14 years@Sparky: True, updated answer with comment to exclude those if necessary.
-
JCasso over 14 years@Jack: yes there is. You may use len function. select * from mytable where msisdn like '56655%' and len(msisdn) = 10
-
Michel de Ruiter almost 8 yearsSQL Server 2005+ include the CLR, 2000 does not.
-
lucidbrot almost 7 yearsWould this also work if the pattern
%7%
would appear multiple times or does PATINDEX only return the first occurrence? E.g.X7XX7XX
, wouldPATINDEX
not return 1 instead of 5?