CODEWITHSUNDEEP
sql-server-2008t-sqlssms
Bill Software Engineer
Bill Software Engineer

Reputation: 7822

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

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

Upvotes: 5

Views: 4805

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

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

  1. In Object Explorer, right-click your table and select Design
  2. Right-click your column in the upper grid and select Check Constraints...
  3. Click Add
  4. Type column_name NOT LIKE '% %' in the "Expression" box (use your actual column name, not column_name)
  5. 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)
  6. Click Close
  7. 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 '% %')

Upvotes: 6

Related Questions