This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demonstration on how to validate the range of IP Address in SQL Server.
You can set CHECK CONSTRAINT for each part of IP Address for validating a proper range.
For example,
You require inserting IP Address between 10.20 range. This you can achieve using PARSENAME function of SQL Server.
Below is a full demo on this:
Let’s first create one table and define CHECK Constraint using PARSENAME.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE tbl_IPAddress ( IPAddress VARCHAR(15) ) GO ALTER TABLE tbl_IPAddress ADD CONSTRAINT chk_tbl_IPAddress_IPAddress CHECK ( (ParseName(IPAddress, 4) = 12) AND (ParseName(IPAddress, 3) = 4) AND (ParseName(IPAddress, 2) BETWEEN 8 AND 26) AND (ParseName(IPAddress, 1) BETWEEN 1 AND 255) ) GO |
You can see different four part validation ,
First two part validation is: only IP Address range start with 12.4
Last two part validation is: 8 to 26 and 1 to 255.
Means valid IP Address are :
12.4.8.125
12.4.15.241
Invalid IP Address are:
10.2.21.52
12.4.7.124
Now insert script for some valid IP Address:
|
1 2 3 4 5 6 7 8 |
INSERT INTO tbl_IPAddress (IPAddress) VALUES ('12.4.8.34') ,('12.4.16.125') ,('12.4.22.120') ,('12.4.22.120') ,('12.4.15.216') GO |
If you tried to insert out of range, then this will show you an error that you cannot violate check constraint.
Script to Select different part of stored IP Address:
|
1 2 3 4 5 6 |
SELECT IPAddress ,ParseName(IPAddress, 4) As FirstPart ,ParseName(IPAddress, 3) As SecondPart ,ParseName(IPAddress, 2) As ThirdPart ,ParseName(IPAddress, 1) As FourthPart FROM tbl_IPAddress |
My Suggestion:
In this demo, I stored IP Address into one VARCHAR(15) column which is not effective storage.
Whenever you don’t require any analysis on IP Address, then you can store the IP Address into one column. Otherwise, please visit this page on the effective storage of IP Addresses.
