regular expression inside SQL Server

Question

Hi!

I need something like this...

stored value in DB is: 5XXXXXX [where x can be any digit]

and i need to match incoming SQL query string like... 5349878.

Does anyone has an idea how to do it?

i have also different cases like XXXX7XX for example, so it has to be generic.

i don't care to represent the pattern in a differnet way inside the SQl server.

i'm working with c# .Net

Thanks For Advance!!!

Answer 1

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]'

Answer 2

SQL Wildcards are enough for this purpose. Follow this link: http://www.w3schools.com/SQL/sql%5Fwildcards.asp

you need to use a query like this:

select * from mytable where msisdn like '%7%'

or

select * from mytable where msisdn like '56655%'

Answer 3

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:

• CHARINDEX
• ISNUMERIC

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:

• PATINDEX

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 like this one, 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.

Answer 4

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...