Posted by Dan Guzman on 10/03/06 01:09

> I think you wanted to include NOT there:

Yes, thanks for the catch, Hugo. I also had an extraneous NO CHECK and '^'
in the expression. The corrected version:

ALTER TABLE Table1 WITH CHECK
ADD CONSTRAINT CK_Table1_Col1
CHECK (Col1 NOT LIKE '%[' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) + ']%')
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:hh33i2p3mo572838m7c7hiiqdj2q8ckber@4ax.com...
> 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: INSER at runtime?
• Prev in forum: CONTAINS in SQL Server
• Thread view: Re: Best way to force a varchar column to have no whitespace

[Reply to this message]