Regular expressions and queries

[mmonclair]

I have a form handling component I'm working on where I want to set up a SELECT statement that will take, in its WHERE clause, a line of text entered from a form and check it against a table with records of regular expressions. The regular expression contains a stub of the particular text. Here's a pseudocode of what I'm trying to do:

set variable.text = "I like foxes and wolves."

SELECT myRecord FROM myTable
WHERE regexField is a match for variable.text

The entry in regexField I'm trying to match variable.text to is "I like [a-z]{1-5} and wolves." Thus, the above would return a record in the query object, but a text entry of "I like giraffes and wolves." would not. I can think of any number of ways to use a regular expression to look for strings in a record, or of using substrings to look for a string in a record, but I'm drawing a blank on how to see if a string being used in the WHERE clause of a SELECT statement can be checked against a regular expression in a database record.

[pritaeas]

What database are you using. MySQL supports regex functionality.

[mmonclair]

Sorry, I should have mentioned it: I'm using MS SQL.

[pritaeas]

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

[mmonclair]

Thanks much for yor reply. I had actually read that article, but decided to keep investigating as the DBAs for my development group are not keen to having us CF developers add more functions to their database (we're a very large and very compartmentalized group). So I had hoped to be able to find an equivalent to a LIKE or IN operator, though in reverse.

I'm going to take another look at the design of my component and see if there's a different way I can approach this question.

[pritaeas]

Ask your DBA's to provide you with a stored procedure where you can pass the regex.

[mmonclair]

I did manage to work out a different design for my application that would avoid having to go this route. Your suggestion is a good one, but when I ran it by the lead developer for my team, he indicated that the DBAs here are loath to do so. We're a huge group, and the DBAs want to keep any such requests to a minimum, and making such requests will have to be approved by those higher than either of us.

BTW, I started here a couple of months ago, my first gig developing for a large corporate entity, after working largely on my own or in small groups, and am running into a problem I've heard developers bemoan for years: the layers of decision making needed to implement a simple change at larger companies.

[pritaeas]

Sad, but true.