CHECK It Out

T-SQL problems have a way of tricking programmers. At first glance, those problems look so simple that you're certain you'll need only a few minutes to solve them. However, after you start working on a solution, you realize that the problem isn't so simple after all. Validating IP addresses can be one of those problems. In particular, you might be stymied when you try to write a CHECK constraint that validates character strings containing IP addresses entered into a table column. I found this challenge interesting because I had to perform T-SQL acrobatics and exhaust my bag of tricks before devising a solution. As I share my conclusions in this article, I assume that you know how to use the LIKE predicate to write simple CHECK constraints and pattern searches and that you're familiar with basic string-manipulation functions such as CHARINDEX(), REPLACE(), SUBSTRING(), LEFT(), and RIGHT(). For each part of the solution, I've provided complete Web listings that you can download from http://www.sqlmag .com, InstantDoc ID 26728.

The Problem
Let's lay the groundwork for the problem by describing an IP address. In binary format, an IP address is composed of four octets, each of which contains 8 bits. An octet can hold 256 (2⁸) different values. Typically, you represent an IP address as w.x.y.z, in which w, x, y, and z are whole numbers in the 0-to-255 range. A common way to store an IP address in a database is in a varchar column in the form 'w.x.y.z'. The following statement creates the IPs table for this article's examples:

CREATE TABLE IPs
( ip varchar(15) NOT NULL CONSTRAINT PK_IPs PRIMARY KEY
   /* other columns */ )

I chose a maximum length of 15 characters for the ip column because each of the four octets can hold a number no longer than three digits, and I used three periods to separate the octets. To verify that only valid IP addresses enter the table, you need to add a CHECK constraint to the table by following the requirements that I just specified. In practice, the requirements for valid IP addresses are a bit more complex. For more information about these requirements, see the Microsoft article "Understanding TCP/IP Addressing and Subnetting Basics" at http://support.microsoft.com/default.aspx ?scid=KB;EN-US;q164015. To write a CHECK constraint that validates an IP address, you need to devise a logical expression that evaluates to TRUE only when the IP address is valid.

The Solution
To devise the logical expression that evaluates to TRUE when the IP address is valid, you need to complete three tasks. For each task, you need to write a simple logical expression, then construct a complex logical expression that combines all the simple logical expressions.

Task 1: Confirm that the input value holds exactly three periods and that no octet is empty. You can script the first task in several ways. The following example shows how you can use the LIKE predicate to verify that the input value holds exactly three periods and that each octet holds at least one value:

ip LIKE '%_%.%_%.%_%.%_%'
AND
ip NOT LIKE '%.%.%.%.%'

Alternatively, you can write the first task by using the REPLACE() function:

LEN(ip) - LEN(REPLACE(ip, '.', '')) = 3
AND
ip NOT LIKE '%..%' AND ip NOT LIKE '.%' 
AND ip NOT LIKE '%.'

In the first line of this code snippet, the REPLACE() function replaces each occurrence of a period in the input value with an empty string. Subtracting the length of the result string from the length of the original string gives you the number of times a period occurs in the original string. The second line of code confirms no empty octets in the middle, beginning, and end of the input value.

Task 2: Extract each octet from the input value. To accomplish the second task, you can start by using the LEFT() function to extract the first octet. You want to express the length of the string that you need to extract from the input value as the position of the first period in the input value minus 1, as the following code shows:

LEFT(ip, CHARINDEX('.', ip) - 1)

To extract the second octet, you can use the SUBSTRING() function. The starting position from which you want to extract the substring is the position of the character after the first period. The following code shows how you can express the length of the substring as the position of the second period, minus the position of the first period, minus 1:

SUBSTRING(
   ip,
   -- Start: position of the first period plus 1
   CHARINDEX('.', ip) + 1,
   -- Length: position of the second period 
   -- minus position of the first period minus 1
   CHARINDEX('.', ip, CHARINDEX('.', ip) + 1) -
      CHARINDEX('.', ip) - 1)

The tricky part here is to find the position of the second period. The CHARINDEX() function has a third argument specifying the character position from which to start searching for the first argument within the second argument. In this case, I specified the position of the first period plus one as the start location.

You can use the SUBSTRING() function again to extract the third octet. You need to express the starting position from which you want to extract the substring as the position of the character after the second period. I showed how to calculate the position of the second period when I extracted the second octet. You can express the length of the substring as the length of the input value, minus the character position of the third period from the end of the string, minus the character position of the second period. Asking for the character position of the third period from the end of the string is the same as asking for the position of the first period in the string in reverse order:

SUBSTRING(
   ip,
   -- Start: position of second period + 1
   CHARINDEX('.', ip, CHARINDEX('.', ip) + 1) + 1,
   -- Length: the length of whole string minus the 
   -- position of the third period from the end minus 
   -- the position of the second period
   LEN(ip) -
      CHARINDEX('.', REVERSE(ip)) -
      CHARINDEX('.', ip, CHARINDEX('.', ip) + 1))

To retrieve the fourth octet, you can use the RIGHT() function. The following code shows how you can express the length of the string as the position of the third period from the end of the string minus 1:

RIGHT(ip, CHARINDEX('.', REVERSE(ip)) - 1)