Better way to handle and validate IP Address data column
One of the developer asked a question to handle and parse the IP Address data on a table, as you are aware IP Address data would have 4 sections and to parse each 4-part value is requried for reduce any duplication. You could take hlep of SUBSTRING and CHARINDEX statements in this case, but to just validate the data you can perform as follows:
,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'
Say for a value of 192.168.1.1 takes the 4 sections between the . and seperates them and only 1 line of code for each section is needed. To avoid return of NULL for any empty data you can take hlep of NULLIF function.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.