How do I make sure a string column has no spaces?

sql-server-2008, ssms, t-sql

Solution

You could add the following check constraint:

`CHECK LEN(col) = LEN(REPLACE(col, ' ', ''));`

...or...

CHECK (col NOT LIKE '% %');

...or...

CHECK (CHARINDEX(' ', col) = 0)

Example:

USE tempdb;
GO

CREATE TABLE dbo.bar(foo NVARCHAR(50) PRIMARY KEY);

ALTER TABLE dbo.bar ADD CONSTRAINT chkNoSpaces
 CHECK (foo NOT LIKE '% %');

Succeeds:

INSERT dbo.bar(foo) SELECT 'AAAA';
GO

Fails:

INSERT dbo.bar(foo) SELECT 'AA AA';
GO

Results:

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "chkNoSpaces". The conflict occurred in database "tempdb", table "dbo.bar", column 'foo'. The statement has been terminated.

Clean up:

DROP TABLE dbo.bar;

EDIT

If you need to do this through the UI for some reason (again I recommend you do this with a script that you can make atomic, repeatable, save to a file, store in source control, etc):

- In Object Explorer, right-click your table and select Design

- Right-click your column in the upper grid and select Check Constraints...

- Click Add

- Type `column_name NOT LIKE '% %'` in the "Expression" box (use your actual column name, not `column_name`)

- If you think you already have data that violates the constraint, change the option for "Check Existing Data..." to No (and promptly go fix that data)

- Click Close

- Click on the "Save" icon on the toolbar

Note that the UI actually changes the construction of the clause, e.g. `(NOT col_name LIKE '% %')`

Problem

I have currently a primary key field of `nvarchar(50)`, I am willing to change the type, if I can get it to accept no white space. Is there anyway to do this? Should allow the following: ``` ------ AAAA BBBB CCCC ``` Should not allow the following: ``` ------ AA AAA BBBB B C CCCC ```

Original source