Disallow any white space in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Disallow any white space in a column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-06 : 08:00:04

Nick writes "I want to make sure that when a record is inserted into a table a column value can not be blank or have any white space such as muliple spaces or line feeds. I want to handle this at the database level instead at the app level."

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 08:06:06

Either you can specify the default value to that columns.

or you can write the trigger for it and Raiseerror if any default value is inserted.

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-06 : 08:07:38

Something like this?

create table #temp
(
	a varchar(200) Check (a not like '%' + char(32) + '%' and a not like '%' + char(13) + '%')
)

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 08:34:41

Multiple spaces? Change harsh's suggestion to

create table #temp
(
	a varchar(200) Check (a not like '%' + char(32) + char(32) + '%' and a not like '%' + char(13) + '%' and a not like '%' + char(10) + '%')
)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-06 : 08:37:48

Peter,

Why two char(32)? I think my suggestion can handle multiple spaces as well...Can you give example which doesn't work with my script?

Harsh Athalye
India.
"Nothing is Impossible"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-11-06 : 08:43:07

"multiple spaces"...your code will prevent single spaces being saved in the data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 08:45:22

Yes. For example a sentence with only one space between each word. <- as this line, no sequential/multiple spaces.
But this line has here two sequantial/multiple spaces.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-06 : 08:53:04

Oh..I got the requirement wrong !!

Thanks peter !

One more addition to prevent blanks:

create table #temp
(
	a varchar(200) Check (a not like '%' + char(32) + char(32) + '%' 
and a not like '%' + char(13) + '%' and a not like '%' + char(10) + '%'  and a <> '')
)

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources