CODEWITHSUNDEEP
sql-serverregex
Jaska
Jaska

Reputation: 1430

SQLServer Regexp empty string matching

using the RegExp capabilities of SQLServer, I need to find all strings that are not starting with the letter 'M', but [^M]% seems to work so that it's searching strings that don't start with 'M', but the string still need's to have at least one character, so this pattern will not return empty strings!!

So is there [^M]% pattern that checks ZERO or ONE character?

p.s. Using NOT LIKE is not an option in my case, nor is any kind of modifications to the where clause except the regexp pattern.

Upvotes: 2

Views: 2432

Answers (3)

Naveed S
Naveed S

Reputation: 5236

For pure regex you would have to use CLR user defined functions.

With regex support you may search for those of the form \b|[^M].* which allows a blank or a non-M character followed by zero or more characters.

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

Upvotes: 1

Alex K.
Alex K.

Reputation: 175826

One way to make [^M]% match '' is to just append a character that is not M to the input;

where Fld + '?' like '[^M]%'

Upvotes: 1

xanatos
xanatos

Reputation: 111870

You could put a condition OR MyField = ''. You are using SQL Server 2012, right? By looking at the help page I don't see any "or" condition for the regexps http://msdn.microsoft.com/en-us/library/ms179859.aspx

Upvotes: 1

Related Questions