Constraint on column

Hello experts I have a database called BadgeDB which has a table dbo.ContractorBadges with a Column of

Name         varchar(255)

The Name field is currently a free form text field. The purpose of this field is to accept both the first and last name of a Contractor.  If only one name is entered instead of first and last our import will fail as it will leave the “last name” within our database blank resulting in the data not coming across our interface.

I would like to put a constraint on this field to ensure that both first and last name are entered – example at least one blank space – is input therefore creating two words or names in the field.  Further enhancement is to create another constraint  to disallow and symbols from being entered in the field.  Symbols such as ! @ # $ % ^ & * ( ) - ? < > / should not be accepted

How would I go about writing both or a single constraints for doing this?

Thank you so much for your time.
algotubeAsked:
Who is Participating?
 
lcohanDatabase AnalystCommented:
Sorry...no double quotes, please replace the command like below with singlequotes then test it with an insert - first will fail with constraint violation as expected, second works as it has a space in it:

use BadgeDB
go

create table test   (name varchar(255) null)
go

--ALTER TABLE test  drop CONSTRAINT CK_Name
ALTER TABLE test   WITH NOCHECK ADD CONSTRAINT CK_Name CHECK (Name like '% %' AND Name NOT like '[!@#$%^&*( )-?< >/]')

insert into test   (name) select '*'
insert into test   (name) select 'first last'

drop table test
0
 
derekkrommCommented:
alter table <tbl> add constraint <constraintname> check (charindex(' ', [Name]) > 0 and charindex('!', [Name]) = 0 and ... )

0
 
babytamCommented:
These kind of constraints should be enforced on the GUI.  You should have two fields on the GUI, First Name and Last Name fields.  When you write it back to the table, you can concatenate the fields together.  This will ensure you have consistent data format.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
lcohanDatabase AnalystCommented:
It should be something like below and keep in mind that you would have to fix all data before adding the constraint if you would want to add it with check:

http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx

use BadgeDB
go
 
ALTER TABLE dbo.ContractorBadges WITH NOCHECK
ADD CONSTRAINT Name CHECK (name like " " AND name NOT like "[!@#$%^&*( )-?< >/]")

0
 
algotubeAuthor Commented:
Thanks everyone for your replies.

Icohan

I am using your example:

use BadgeDB
go
 
ALTER TABLE dbo.ContractorBadges WITH NOCHECK
ADD CONSTRAINT CK_Name CHECK (Name like " " AND Name NOT like "[!@#$%^&*( )-?< >/]")


and I keep on getting:

Msg 207, Level 16, State 1, Line 3
Invalid column name ' '.
Msg 207, Level 16, State 1, Line 3
Invalid column name '[!@#$%^&*( )-?< >/]'.

I checked again and the column is Name (varchar(255)......what am I missing here.........???

Thank you
0
 
algotubeAuthor Commented:
Also, tried

use BadgeDB
go
 
ALTER TABLE dbo.ContractorBadges WITH NOCHECK
ADD CONSTRAINT Name CHECK (Name like " " AND Name NOT like "[!@#$%^&*( )-?< >/]")

stell getting:

Msg 207, Level 16, State 1, Line 4
Invalid column name ' '.
Msg 207, Level 16, State 1, Line 4
Invalid column name '[!@#$%^&*( )-?< >/]'.
0
 
algotubeAuthor Commented:
lcohan

   Thank you so much for your help very much appreciated.

The first part worked like a charm we entered -- example

Sam -- failed with constraint violation
Sam Preston -- worked

when we entered:

Sam # or Sam @123  or Sam &Preston -- this still worked were it should have given us a failer because of the AND Name NOT like '[!@#$%^&*( )-?< >/]') condition

Sorry, with I could award more points............ for you....... do I need a comma between the symbals or something????
0
 
algotubeAuthor Commented:
Sorry, with I could award more points............ for you....... do I need a comma between the symbals or something????

should read

Sorry, wish I could award more points............ for you....... do I need a comma between the symbals or something????
0
 
lcohanDatabase AnalystCommented:
That's hlf true because if you try

insert into test   (name) select 'sam*' you get the error
insert into test   (name) select 'sam *' indeed it works and let me try diffrenet constraint
0
 
algotubeAuthor Commented:

Thanks again Icohan
0
 
lcohanDatabase AnalystCommented:
Well, I tried all posible combinations I can think of however can't find a combination to suit all you need by using only one column so I suggest you use two columns like first_name, last_name and put one constraint on each column to NOT allow space and any of the chars you listed above.
0
 
algotubeAuthor Commented:
Sounds like a plan, babytam also suggested this and I believe it's the way to go.  I will explain this to my developers and get back to you. Thanks again for looking into this.
0
 
algotubeAuthor Commented:
Thanks again for your time and help very much appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.