CHECK constraint - SQL Server Forums

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)

CHECK constraint

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-01-17 : 07:26:26

I want to have a check constraint on a column to force the user or other queries that only enter printable ascii symbol from space to ~, including all alphanumerics. i tried this:

CREATE TABLE tbl (
id int IDENTITY,
col varchar(50) CONSTRAINT CK_t1 CHECK (col not like '%[^ -~A-Z0-9]%')
)

with this check it generates an error for all characters below Ascii(32) (space) except for char(9), char(10), char(11), char(12) and char(13). i give an error for this insert statement correctly:

INSERT INTO tbl(col) VALUES('abc' + char(20))

but unfortunately this one

INSERT INTO tbl(col) VALUES('abc' + char(9))

will be executed and adds a row to the table. what's wrong with check constraint and how can i correct it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 07:36:40

You know that a minus sign mean a range?
In your case a range from "space" (ascii 32) to "~" (ascii 126).
Range A-Z falls into this category already with ascii 65 to 90.
Range 0-9 falls into this category already with ascii 48 to 57.

E 12°55'05.63"
N 56°04'39.26"

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-01-17 : 07:53:55

i find out that if i exclude A-Z0-9 from check constraint i can enter all characters in that columns. even if it is redundant i wonder why char(9) to char(13) can be entered in that column. it seems the constraint dictates these characters as forbiden ones but in practice it is not. can you help me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 08:20:56

Some of the characters are actually wildcard and other characters have other meanings.
This is the shortest CHECK pattern I can come up with right now to accomplish your task.

Execute these lines one by one.

CREATE TABLE	#Sample
		(
			col VARCHAR(50) CONSTRAINT CK_t1 CHECK (col NOT LIKE '%[^ !-$¦%&-,¦-.-Z¦[\¦]^¦_`-%' ESCAPE '¦'),
			vis AS '_' + col + '_',
			[ascii value] AS ASCII(RIGHT(col, 1))
		)

INSERT	#Sample
SELECT	'ABC'

INSERT	#Sample
SELECT	'ABC' + CHAR(32)

INSERT	#Sample
SELECT	'ABC' + CHAR(126)

INSERT	#Sample
SELECT	'ABC' + CHAR(36)

INSERT	#Sample
SELECT	'ABC' + CHAR(37)

INSERT	#Sample
SELECT	'ABC' + CHAR(20)

INSERT	#Sample
SELECT	'ABC' + CHAR(9)

INSERT	#Sample
SELECT	'ABC' + CHAR(7)

INSERT	#Sample
SELECT	'ABC' + CHAR(127)

SELECT	*
FROM	#Sample

DROP TABLE	#Sample

E 12°55'05.63"
N 56°04'39.26"

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-01-17 : 08:36:17

thanks Peso but after running your code these inserts

INSERT #Sample
SELECT 'ABC' + CHAR(20)

INSERT #Sample
SELECT 'ABC' + CHAR(9)

INSERT #Sample
SELECT 'ABC' + CHAR(7)

ran successfully, (not desired). have i missed any point?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 09:34:05

I have tested now. I didn't use my code, but copied from this page.
This is the output I get for the SELECT statement.

col	vis	ascii value
ABC	_ABC_	67
ABC 	_ABC _	32
ABC~	_ABC~_	126
ABC$	_ABC$_	36
ABC%	_ABC%_	37

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-17 : 12:01:48

Where did you go?

E 12°55'05.63"
N 56°04'39.26"

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-01-18 : 05:33:35

I'm confusing. I copied and pasted your code into QA. And this is the output I got in QA for the SELECT statement:

col vis ascii value
------- ---------- -----------
ABC _ABC_ 67
ABC _ABC _ 32
ABC~ _ABC~_ 126
ABC$ _ABC$_ 36
ABC% _ABC%_ 37
ABC _ABC_ 20
ABC _ABC _ 9
ABC _ABC_ 7
ABC _ABC_ 127

I repeat again. I exactly ran your code without even a bit of change. Can you tell me what's going wrong on my side

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-18 : 13:16:21

Maybe we are using a different collation?

CREATE TABLE	#Sample
		(
			col VARCHAR(50) COLLATE FINNISH_SWEDISH_CS_AS CONSTRAINT CK_t1 CHECK (col NOT LIKE '%[^ !-$¦%&-,¦-.-Z¦[\¦]^¦_`-%' ESCAPE '¦'),
			vis AS '_' + col + '_',
			[ascii value] AS ASCII(RIGHT(col, 1))
		)

INSERT	#Sample
SELECT	'ABC'

INSERT	#Sample
SELECT	'ABC' + CHAR(32)

INSERT	#Sample
SELECT	'ABC' + CHAR(126)

INSERT	#Sample
SELECT	'ABC' + CHAR(36)

INSERT	#Sample
SELECT	'ABC' + CHAR(37)

INSERT	#Sample
SELECT	'ABC' + CHAR(20)

INSERT	#Sample
SELECT	'ABC' + CHAR(9)

INSERT	#Sample
SELECT	'ABC' + CHAR(7)

INSERT	#Sample
SELECT	'ABC' + CHAR(127)

SELECT	*
FROM	#Sample

DROP TABLE	#Sample

E 12°55'05.63"
N 56°04'39.26"

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-01-19 : 01:25:25

With this change I got the same result as your. But I am using SQL_Latin1_General_CP1256_CI_AS collate. If you try this

CREATE TABLE #Sample
(
col VARCHAR(50) COLLATE SQL_Latin1_General_CP1256_CI_AS CONSTRAINT CK_t1 CHECK (col NOT LIKE '%[^ !-$¦%&-,¦-.-Z¦[\¦]^¦_`-%' ESCAPE '¦'),
vis AS '_' + col + '_',
[ascii value] AS ASCII(RIGHT(col, 1))
)

you will see that unwanted characters will be inserted. Is there any workaround for this collation. and why should special charcters be depended on collations. I thought TAB, Line Feed and so are the same in any collation. And thanks for your replies.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 02:57:40

This works. You have to use a unicode check constraint.

CREATE TABLE	#Sample
		(
			col VARCHAR(50) COLLATE SQL_Latin1_General_CP1256_CI_AS CONSTRAINT CK_t1 CHECK (col NOT LIKE N'%[^ !-$¦%&-,¦-.-Z¦[\¦]^¦_`-%' ESCAPE '¦'),
			vis AS '_' + col + '_',
			[ascii value] AS ASCII(RIGHT(col, 1))
		)

INSERT	#Sample
SELECT	'ABC'

INSERT	#Sample
SELECT	'ABC' + CHAR(32)

INSERT	#Sample
SELECT	'ABC' + CHAR(126)

INSERT	#Sample
SELECT	'ABC' + CHAR(36)

INSERT	#Sample
SELECT	'ABC' + CHAR(37)

INSERT	#Sample
SELECT	'ABC' + CHAR(20)

INSERT	#Sample
SELECT	'ABC' + CHAR(9)

INSERT	#Sample
SELECT	'ABC' + CHAR(7)

INSERT	#Sample
SELECT	'ABC' + CHAR(127)

SELECT	*
FROM	#Sample

DROP TABLE	#Sample

E 12°55'05.63"
N 56°04'39.26"

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2009-01-19 : 04:34:28

Finally i got it your result too. Thanks. Isnt there any more readable and straightforward way to implement this CHECK constraint?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-19 : 05:35:30

This is the fastest way to use your check contraint.
You can however make an UDF to check your data, but it's cumbersome to edit.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources