asked
Regexmatches
Hello, I have a validation task that says only AAAA-ZZZZ values to be allowed in a field. Here I have to validate using the regular expression for the field that can contain values like $%09878 etc. Can anyone help me on the syntax part ? I have tried using RegexMatches function in SSMS but it returns an Ambigious error. Thanks in Advance. Ramya
Can you give more details about this RegexMatches function? Where is this defined? What is the error message?
I'm not entirely sure what you're asking for - your question seems a little self-contradictory. However, I'll have a stab at an answer! There's always the T-SQL [`LIKE`][1] operator - that'll do limited reg-ex matching. For example SELECT * FROM foo WHERE bar LIKE '[a-z][a-z][a-z][a-z]' will only return records from table "foo" where bar is a four alpha character string. If you need each character to match, then add extra bits to the WHERE clause to specify that [`SUBSTRING (bar,1,1)`][2] = `SUBSTRING (bar, 2, 1)` etc. You should be able to encapsulate that logic into a [`CHECK CONSTRAINT`][3] to enforce that logic on the table itself. [1]:
http://msdn.microsoft.com/en-us/library/ms179859.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms187748.aspx [3]:
http://msdn.microsoft.com/en-us/library/ms188258.aspx
If you're just after a regexp for 4 character A-Z then I'd use '^[A-Z]{4}$' (or '^[A-z]{4}$' for case insensitive). For multiple characters (as mentioned, your question is a little ambiguous on this point), try '^[A-Z]+$'
