Validating phone format. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Validating phone format.

Author

Topic

onkabetse
Starting Member

5 Posts

Posted - 2011-04-27 : 13:04:35

I have created a table with a column phone number. The same column need to be validated to accept phone numbers in the format '[0-9][0-9],[0-9][0-9],[0-9][0-9],[0-9][0-9],[0-9]' Example 11-111-1111-111-111
Help me on how to validate the column to accept this.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-27 : 13:10:31

you mean with a constraint??

You would really be better off doing this in the interface layer... ie. where the data is being entered.


Declare @t table (
	id int identity(1,1),
	phone varchar(20),
	Primary Key (id),
	CHECK (phone like replicate('[0-9]',10) )
)

Insert Into @t Select '0123456789'
Insert Into @t Select '012345678'
Insert Into @t Select '01234567899'
Insert Into @t Select '0000000000'

Select * From @t

Corey

I Has Returned!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-27 : 13:12:13

quote:
Originally posted by onkabetse

I have created a table with a column phone number. The same column need to be validated to accept phone numbers in the format '[0-9][0-9],[0-9][0-9],[0-9][0-9],[0-9][0-9],[0-9]' Example 11-111-1111-111-111
Help me on how to validate the column to accept this.

P.S. your sample format and sample data do not match... not sure if that was intentional or not.

Corey

I Has Returned!!

onkabetse
Starting Member

5 Posts

Posted - 2011-04-27 : 13:49:33

quote:
Originally posted by Seventhnight

you mean with a constraint??

You would really be better off doing this in the interface layer... ie. where the data is being entered.

Declare @t table (
id int identity(1,1),
phone varchar(20),
Primary Key (id),
CHECK (phone like replicate('[0-9]',10) )
)

Insert Into @t Select '0123456789'
Insert Into @t Select '012345678'
Insert Into @t Select '01234567899'
Insert Into @t Select '0000000000'

Select * From @t

Corey

I Has Returned!!

Thanx... But I mean validating using constraints because I am still new and I have not started working on interface... Hope you will respond based on constraints

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-27 : 15:15:34

I gave you a sample of a constraint that only allows a particular format...

As for validation... generally, that is an interface concept. Collect Data -> Evaluate Data -> Accept/Deny Data -> Show Friendly Message

Sql doesn't work like that. You can ensure data integrity... but it's not so simple to 'Validate' data.

What is your plan for when the data is 'InValid'??

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources