CODEWITHSUNDEEP
sqlsql-serverwildcard
Anjali5
Anjali5

Reputation: 683

Regular expression filter

I have this regular expression in my sql query

DECLARE @RETURN_VALUE VARCHAR(MAX)  
IF @value LIKE '%[0-9]%[^A-Z]%[0-9]%'
BEGIN
    SET @RETURN_VALUE = NULL
END

I am not sure, but whenever I have this in my row 12 TEST then it gives me the value of 12, but if I have three digit number then it filters out the three digit numbers.How can I modify the regular expression to return me the three digits numbers too.

any help will be appreciated.

Upvotes: 1

Views: 14402

Answers (4)

Nicholas Carey
Nicholas Carey

Reputation: 74267

SQL doesn't have regular expressions: it has SQL wildcard expressions. They are much simpler than regular expressions and long predate regular expressions. For instance, there is no way to specify alternation (a|b) or repetition ( a*, a+, a?, a{m,n} ) such as you might find in a regular expression.

The 'like expression' that you have 

LIKE '%[0-9]%[^A-Z]%[0-9]%'

will match any string containing the following pattern anywhere in the string

  • zero or more of any character, followed by...
  • a single decimal digit, followed by...
  • zero or more of any character, followed by...
  • a single character other than A–Z (whether it's case sensitive or not depends on the collating sequence in use), followed by...
  • zero or of any character, followed by...
  • a single decimal digit, followed by...
  • zero or more of any character

One should note that the % is likely to match perhaps more than you might like.

Upvotes: 4

Justin Pihony
Justin Pihony

Reputation: 67075

Have you tried ([0-9]*). I believe that this will capture every digit for you. However, I am not as strong at regex. When I ran this through rubular, it worked, though :) BTW, rubular is a great way to test out regular expressions

Upvotes: 0

Michael Edenfield
Michael Edenfield

Reputation: 28338

First of all, note that this is not really a "regular expression", it's a SQL-specific form of wildcard matching. You are very limited in what you can accomplish with SQL wildcards. As one example, you cannot "optionally" match a specific character or character set.

Your expression, as you've written it, will match any value that contains two digits with at least one non-letter character in between them, meaning it will match:

  • 111
  • 1^1
  • 1?7
  • 1AAAAAAAAAAA?AAAAAAAAA1
  • -----------------------5-----------------3-------

And infinitely more items of a similar structure.

Oddly, one string that would not match this pattern is "12 TEST" because there is no character between the 1 and 2. The pattern also won't "give you" the value of 12 back because it's not a parsing expression, just a matching expression: it returns 1 (true) or 0 (false).

There is clearly something else going on in your application, possibly even an actual regular expression, but it has nothing to do with the SQL you've included here.

Upvotes: 0

Chris Benard
Chris Benard

Reputation: 3215

You can easily create a SQL CLR function and use this in your queries. Visual Studio has a project template for this and makes deploying the functions a snap.

Here is more information from Microsoft about how to create the function and how to use it (for boolean matches and for data extraction).

Upvotes: 0

Related Questions