In this post, I provide you one demo script which validate an IP Address range before insert.
You can set check constraint for each part of IP Address to validate a proper range.
You require to insert the IP Address between 10.20 range. This you can achieve using PARSENAME function of SQL Server.
Below is a full demo for this:
Let’s first create one table and define CHECK Constraint using PARSENAME.
CREATE TABLE tbl_IPAddress
ALTER TABLE tbl_IPAddress ADD CONSTRAINT chk_tbl_IPAddress_IPAddress
(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)
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 . 1 to 255.
Means valid IP Address are :
Invalid IP Address are:
Now insert script for some valid IP Address:
INSERT INTO tbl_IPAddress (IPAddress)
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:
,ParseName(IPAddress, 4) As FirstPart
,ParseName(IPAddress, 3) As SecondPart
,ParseName(IPAddress, 2) As ThirdPart
,ParseName(IPAddress, 1) As FourthPart
In this demo I have stored IP Address into one VARCHAR(15) column which is not effective storage. Whenever you don’t require any analytics process on IP Address then you can store the IP Address into one column. Otherwise, please visit this page to learn effective storage of IP Addresses.
Other Related Articles :
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.