Pattern matching in check constraint

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pattern matching in check constraint

Author

Topic

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2003-06-24 : 12:01:25

So far, I've only been able to perform a limited amount of pattern matching when establishing my check constraints in a CREATE TABLE statement. When I need a column with a datatype of numeric(1,0) to be between 3 and 7 I use:

(col1 = '[3-7]')

In a UserID field when I need to ensure that the first character is not a number I use:

(UserID LIKE '[a-z]%')

But, I'm having trouble finding an example or reference that shows me how to check the value of a single character, for ALL characters in a **variable length** string, to ensure that only certain characters are used. It's an "email" field and I'd like to enforce data integrity by limiting it to only the valid email addy characters. I've tried a number of possibilities to no avail.

Should I look into the PATINDEX function, or is there an easier way to do this? If I can't find a solution here, I'm suspecting I'll have to use something similar to

(col1 LIKE '[a-z,0-9,CHAR(64),_,-]') /* don't hold me to my syntax here */

for each character in the string, up to the maximum allowable length of the field? This is an aweful long string for checking the value and I'm hoping that I just haven't been able to find the right documentation.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-24 : 13:12:19

You could use a tally table to look at each character in a string ...


declare @v varchar(100)
select @v = 'find the !'

select 'Invalid character:' + convert(varchar,n), substring(@v,n,1)
from dbo.tally
where n <= datalength(@v) and
      substring(@v,n,1) not like '[a-z A-Z]'

...or take a look at the recent article about a function to instantiate a VBScript regular expression object.

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-24 : 13:48:11

To check that only certain characters are used just make sure that other ones aren't

e.g. to make sure b,c,d,e

to check @str

declare @str varchar(20)
select @str = 'cbcbjcdbd'

select 'bad' where patindex('%[^b-e]%',@str) <> 0

for more complicated things

declare @s varchar(100)
select @s = '%[' + char(0) + '-' + char(ascii('b')-1) + char(ascii('e')+1) + '-' + char(255) + ']%'
select 'bad' where patindex(@s,@str) <> 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 06/24/2003 13:50:04

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources