Best way to force a varchar column to have no whitespace

[metaperl]

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

[Ed Murphy]

metaperl wrote:

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.

[Dan Guzman]

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" <me******@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

>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

[Hugo Kornelis]

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

[Dan Guzman]

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" <hu**@perFact.REMOVETHIS.info.INVALIDwrote in message
news:hh********************************@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

[--CELKO--]

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

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