Script to Validate IP Address range in SQL Server

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.

E.g,

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. 

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 :

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:

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:

My Suggestion:

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 :

Anvesh Patel

Leave a Reply

1 Comment on "Script to Validate IP Address range in SQL Server"

Notify of
avatar
Sort by:   newest | oldest | most voted
Raquel
Guest

Thanks Anvesh! you helped me lot…..I added your blog into my bookmark.

wpDiscuz