Allow certain 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

SQL Server 2008 Forums

Transact-SQL (2008)

Allow certain Format

Author

Topic

MrCapuchino
Starting Member

9 Posts

Posted - 2010-09-19 : 21:27:03

Hello I want to create a datatype that for phone number.

This needs to accept only phone number in the following format:

xx-xxx-xxxx-xxx-xxx

X = Number

The dashes count too.

I just can't remember how to do it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-19 : 21:32:38

char(19) plus a check constraint? Or do you want a user-defined data type (and why!)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sachin.Nand

2937 Posts

Posted - 2010-09-20 : 03:26:39

This will give you a rough idea

1)Create a userdefined function to check your email format


CREATE FUNCTION dbo.udf_IsEMailValid
(
@EMail VARCHAR(100)
)
RETURNS INT
BEGIN
DECLARE @Ret INT
(your email validation logic)
if EMail is valid
SET @Ret = 1
ELSE
SET @Ret = 0
RETURN @Ret
END
GO

2)Then create your table with a check constraint using the UDf you created.


CREATE TABLE YourTable
(

Email VARCHAR(100),
CHECK(dbo.udf_IsEMailValid(Email) = 1)
)
GO

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources