The third problem is that using the ISNUMERIC() function requires a lengthy logical expression that would be hard to maintain, and a combination of all the logical expressions you've written so far already generates a long complex expression. In short, writing a logical expression based on a pattern search that makes certain that each octet can be converted to a valid integer is easier. The following expression confirms a possible conversion by disallowing any characters other than digits and periods:
ip NOT LIKE '%[^0-9.]%'
Now you can revise the CHECK constraint in Web Listing 1 by adding the above logical expression. Note that you should also revise the constraint so that it uses the CASE expression to ensure that SQL Server converts the octets to integer values only after it checks all the other logical expressions. Run the script that Web Listing 2 shows to implement the revised CHECK constraint. Next, run the following code, which attempts to enter an IP address containing an invalid character:
INSERT INTO IPs VALUES('a.1.1.1')
PRINT 'After INSERT'
Notice that you receive an error message that reports a CHECK constraint violation, and SQL Server executes the PRINT statement.
Simplifying the Solution
Although you've added to the table column a CHECK constraint that successfully filters out invalid IP addresses, the constraint's logical expression is hard to read and maintain because it is long and complex. The longest and most complex part of the code extracts the octets from the original input value. You can simplify this section of code in a couple of ways. The first approach is to look for elements in T-SQL that already handle the extraction of certain parts of a string. When I was looking for a simplified solution, I noticed that an IP address looks very similar to another construct in T-SQL—a four-part object name, which is depicted as server.database.owner
.object. T-SQL provides a function called PARSENAME() to parse each part of an object name. The function's syntax is PARSENAME ('object_name', object_piece). In this syntax, object_name is the four-part name, and object_piece is an integer value in the range 1 to 4 where you specify which object part you want to extract (1 object, 2 owner, 3 database, 4 server). To extract the nth octet of an IP address, you invoke the PARSENAME() function as follows:
PARSENAME (, 5 - n).
Web Listing 3 shows how to implement the revised CHECK constraint that uses the PARSNAME() function.
The second approach to simplifying the problematic part of the CHECK constraint in Web Listing 2 is to write logical expressions based on pattern searches only. In this approach, you verify that an octet is a number in the range 0 to 255 without converting it to an integer. You already wrote logical expressions that confirm that the octets are composed only of digits. Now you can add a logical expression that verifies that a number contains no more than three digits:
ip NOT LIKE '%[0-9][0-9][0-9][0-9]%'
an expression that reports no numbers in the range 300 to 999:
ip NOT LIKE '%[3-9][0-9][0-9]%'
one that confirms no numbers in the range 260 to 299:
ip NOT LIKE '%2\[6-9][0-9]%'
and finally, an expression that finds no numbers in the range 256 to 259:
ip NOT LIKE '%25\[6-9]%'
Because the above logical expressions replace the section of code that extracts the octets and converts them to integers, conversion errors are eliminated, and the CHECK constraint can check each logical expression independently without regard to the order of validation. In short, you don't need to use a CASE expression to force the order of validation. Web Listing 4 shows how to implement the revised CHECK constraint.
A Completely Different Approach
If you'd rather avoid the methods I've discussed so far—and the option is available to you—consider not storing the IP addresses in one varchar column. For example, you can store each octet in a separate column that has a data type of tinyint and add a computed column that concatenates all octets and periods to generate a formatted IP address for presentation purposes. The great thing about this approach is that the tinyint data type accepts only values in the range 0 to 255. No validation is required. Web Listing 5 shows how to create the revised IPs table and populate it with a few valid IP addresses. This solution is easier than the others and can save you time and effort.
The Road to the Solution
Sometimes you can learn more from the process that leads to the solution than from the solution itself. Trying to improve the problematic parts of your code is always a good idea, but experimenting with radically different approaches than the one you started with can also yield spectacular results. By taking a different approach, you might find a simple solution that eclipses the original.