Regular expressions in Sql Server?

Hi,

I'm using MS SQL Server 2000 and am trying to execute a select where I want a column to match this regular expression:

((\w)*\|)*$KEY(\|(\w)*)*

as would be defined in Perl (btw, the "|" is a pipe, not an L :))

$KEY would be subsituted by some text I'm querying for.

I tried using LIKE with "[(%|)[]]$KEY[[](|%)]", eg:

select * from mytable m
where m.a like '[(%|)[]]$KEY[[](|%)]'

but it didn't work (again, the $KEY is replaced with text when I'm executing. I just have it here for an example). Is there any way I could get around this problem? I would really appreciate any help.

Thanksthe best you will do is "%|$Key|%", ie, a string that starts with something, has a pipe char, the string you are looking for, a pipe char, ending with something.

I believe "\|" in perl is how you specify a literal "|".

Regular expressions are not supported in TSQL! There are some simple expressions but not what a perl or python programmer is used to.