Best way to force a varchar column to have no whitespace

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Regards,
Terrencemetaperl wrote:

Quote:

Originally Posted by

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Use an update trigger to rewrite it with any whitespace stripped out.|||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

--
Hope this helps.

Dan Guzman
SQL Server MVP

"metaperl" <metaperl@.gmail.comwrote in message
news:1159777716.594197.268970@.e3g2000cwe.googlegro ups.com...

Quote:

Originally Posted by

>I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.
>
Regards,
Terrence
>

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

Quote:

Originally Posted by

>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|||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.INVALIDwrote in message
news:hh33i2p3mo572838m7c7hiiqdj2q8ckber@.4ax.com...

Quote:

Originally Posted by

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

Quote:

Originally Posted by

>>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

|||CONSTRAINT no_white_space
CHECK ( LEW(foo) = LEN (REPLACE (foo, ' ', '')))

You can then nest calls to REPLACE() for tabs,newlines, etc. easily.