Posted by Hugo Kornelis on 10/02/06 22:11

On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:

>One method is with a CHECK constraint that checks for the list of your
>prohibited characters. You may need to scrub data before adding the
>constraint.
>
>ALTER TABLE MyTable WITH CHECK
>ADD CONSTRAINT CK_MyTable_MyColumn
> CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
>']%')
> WITH CHECK
>GO

Hi Dan,

I think you wanted to include NOT there:

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyColumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO

BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.

--
Hugo Kornelis, SQL Server MVP

• Next in forum: Re: Blank Password Policy problem
• Prev in forum: Re: Blank Password Policy problem
• Thread view: Re: Best way to force a varchar column to have no whitespace

[Reply to this message]