CODEWITHSUNDEEP
sql-servert-sql
Ian Warburton
Ian Warburton

Reputation: 15676

How is SQL Server removing spaces?

I have the following constraint...

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [CK_No_Spaces] CHECK  ((charindex(' ',[mobile])=(0)))

If I run the following in Management Studio, it fails.

UPDATE table1 SET mobile = '1 2 3'

If I run the same query from an app, such that the query appears in the profiler and the spaces are in the string, it automatically strips them out.

How is this possible?

It does it on my staging database but not on my local dev database.

EDIT: It seems that the staging database isn't throwing an exception. And the update doesn't happen after all.

Upvotes: 0

Views: 63

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

seems you are trying to update your column 'mobile' to restrict values to only allow with spaces.

using this one, will allow '1 2 3'

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [CK_No_Spaces] CHECK  ((charindex(' ',[mobile])>(0)))

using this one, will restrict '1 2 3'

ALTER TABLE [dbo].[table1]  WITH CHECK ADD  CONSTRAINT [CK_No_Spaces] CHECK  ((charindex(' ',[mobile])=(0)))

You need to check your staging and local databases first.

select count(1) from table1 where charindex(' ',[mobile])>0

Upvotes: 1

Related Questions