Text/Number Formats - 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

Text/Number Formats

Author

Topic

Poddy88
Starting Member

26 Posts

Posted - 2009-02-03 : 19:25:26

Im creating an sql database, but im in need of format validation. I am trying to do the format of an IP address example 221.293.182.12 and also a MAC address example A2:38:C9:13:9A can anybody help with this???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 19:27:21

Your question is not clear. How is the data being stored?

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

Subscribe to my blog

Poddy88
Starting Member

26 Posts

Posted - 2009-02-03 : 19:29:43

Well the IP address would be stored as a number while the Mac would have to be done as a text, if that is what you ment

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 19:30:17

Okay, then how is the data being sent from the application?

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

Subscribe to my blog

Poddy88
Starting Member

26 Posts

Posted - 2009-02-03 : 19:32:25

Not sure what you mean im new to this, but im just going to create a the table, upload it to a webserver and then upload INSERTS to it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 02:58:56

what do you mean by upload inserts to webserver? do you mean uploading table data to file or something? or do you just want to display table data in web?

Poddy88
Starting Member

26 Posts

Posted - 2009-02-04 : 06:45:51

All i want to do it put a valiation on a field, that when an input is done into the table this rule must be applied

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 08:44:51

seems like what you need is a check constraint on column of table. Use a UDF for including logic of validation and refer it in CHECK constraint

Poddy88
Starting Member

26 Posts

Posted - 2009-02-04 : 09:45:09

how would i do that? sry for being a noob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:48:19

something like

ALTER TABLE YourTable ADD CONSTRAINT CHK_Col  CHECK (dbo.ValidateCol(ColumnName)=1)

then write validation code inside udf ValidateCol to return 1 if its in correct format

Poddy88
Starting Member

26 Posts

Posted - 2009-02-04 : 12:05:46

But how would i do a format code? for an ip address or mac address

Poddy88
Starting Member

26 Posts

Posted - 2009-02-04 : 12:09:34

CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30)
IP_Address interger CHECK (???????????????????));

I need the format to ###.###.###.###

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:11:11

you need to write that logic inside UDF. I'm not sure about what all can be your acceptable formats.

Poddy88
Starting Member

26 Posts

Posted - 2009-02-04 : 12:18:46

what formats are there? is there a website i could check so i have a better understanding

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:36:38

for normal IPs this is enough


ALTER TABLE YourTable 
ADD CONSTRAINT CHK_Col  
CHECK ((PARSENAME(ColumnName,4) BETWEEN 0 AND 255
       AND PARSENAME(ColumnName,3) BETWEEN 0 AND 255
       AND PARSENAME(ColumnName,4) BETWEEN 0 AND 255
       AND PARSENAME(ColumnName,4) BETWEEN 0 AND 255)
  )

i'm not sure about MAC address ranges

Poddy88
Starting Member

26 Posts

Posted - 2009-02-04 : 12:47:39

Wouldnt that mean that the IP address is in different fields? what is PARSENAME?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 12:54:04

nope. PARSENAME takes the individual parts of IP value in your IP_Address column


ALTER TABLE Customer 
ADD CONSTRAINT CHK_IP_Address   
CHECK ((PARSENAME(IP_Address ,4) BETWEEN 0 AND 255
       AND PARSENAME(IP_Address ,3) BETWEEN 0 AND 255
       AND PARSENAME(IP_Address ,4) BETWEEN 0 AND 255
       AND PARSENAME(IP_Address ,4) BETWEEN 0 AND 255)
  )

also it would be much easier if you can do this validation at your front end.

Poddy88
Starting Member

26 Posts

Posted - 2009-02-05 : 07:25:38

Ok so if thats taking individual parts of the ip address what is the bit withing the brackets (IP_Address ,3) what is that number for???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 07:34:03

see this
http://doc.ddart.net/mssql/sql70/pa-pt.htm

Poddy88
Starting Member

26 Posts

Posted - 2009-02-05 : 11:18:01