Regular expressions inside SQL Server

Question

I have stored values in my database that look like 5XXXXXX  where X can be any digit  In other words  I need to match incoming SQL query strings like 5349878    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

User · Answer

SQL Wildcards are enough for this purpose. Follow this link: http://www.w3schools.com/SQL/sql_wildcards.asp

you need to use a query like this:

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

or

select * from mytable where msisdn like '56655%'

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Regular expressions inside SQL Server

Examples related to sql

Examples related to sql-server

Examples related to regex

Examples related to expression