How can I use regular expressions in WHERE clause directly?

How can I use regular expressions in WHERE clause directly?

select * from mytbl where myfld like '[0-9]*key'

Sorry - AFAIK SQL does not allow regular expressions in the WHERE claus.

You have to search for the closest match to your regex which you can express with the LIKE patterns and do the rest of the filtering on the client side.

--
SvenC

|||

this is not allowed..there is a turnaround though..

u can prepare the query and assign it to a variable and then use the 'execute' command to run it, takin ur example..

declare @.sql varchar(100)

set @.sql = 'select * from mytbl where myfld like '[0-9]*key''

execute(@.sql)

in addition u can also use variables while to prepare a query..eg u can take the column to compare in a variable to make it more dynamic

declare @.column varchar(10)

set @.column = myfld

set @.sql = 'select * from mytbl where' + @.column+ ' like '[0-9]*key''

|||

SQL Server doesn't directly support regular expressions. But it's easy to add them: just create a User Defined Function (UDF) which maps onto a .Net method. And >net does support regular expressions out of the box. To get an idea of how to do this, check this article:

http://www.u2u.be/Article.aspx?ART=RegExp