Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-20 : 09:44:05
|
| Andy writes "I have a varchar(50) column that can contain only the following values [0-9,A-Z,#,\,/,-,!,,'' '']What's the best way to do this?This doesn't workCONSTRAINT CK_Field1 CHECK (Field1 LIKE '[A-Z,#,\,/,-,!,,'' '']' ESCAPE '!' )Because it only allows 1 character if I repeat the '[A-Z,#,\,/,-,!,,'' '']' portion 50 times it requires 50 characters.Using a UDF in the check constraint would work although I haven't tried it... I was really hoping there is a way to do this using a set based operation." |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-03-20 : 10:20:27
|
| I think this came up here before....use the search facility for something like (excuse the pun)...."edit-pattern"...going back about 3-4 months.I think arnoldfribble came up with a UDF/SP to accomplish this. (sorry for any misquotes) |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-03-20 : 10:52:59
|
| Actually I don't see any reason why something like this wouldn't work:constraint CK_Field1 CHECK (Field1 NOT LIKE '%[^A-Z,#\/-''! ]%' ESCAPE '!' ) |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-03-20 : 11:06:53
|
| You seem to have a number of extraneous commas in your like. Assuming you meant that all the characters must be in [0-9A-Z#\/-, ] the thing to do is turn the constraint around from 'All characters in this set' to 'No characters in the complement of this set'Field1 NOT LIKE '%[^0-9A-Z#\/-, ]%'Argh! Beaten!Edited by - Arnold Fribble on 03/20/2002 11:07:25 |
|
|
|
|
|
|
|
|
Pattern matching with Like