Binaryworld - How to verify the validity of an IP address presented as a string? The ... [ SQL Server -> T-SQL ]

declare @ip varchar(15)
select @ip = '2.1.232.22'
-- Values to check: '2.1.232.22' '2....' '...' '.1.2.' '988.888.882.01' 'a.b.g.e2'
-- This logic fails for values like: '2.1' '.1', but this can be easily accomodated.
-- The reason behind this single IF check is to enable this to be coded in a CHECK
-- constraint for a field directly as opposed to writing a SP or put in a rule maybe
if (/* This one checks in a generic way if each octet in the IP address is a valid numeric
digit. The reverse() function in the 2nd part of the check is an easy way to get to
the last 2 octets in the IP address
*/
@ip like replicate('[0-9]', len(substring(@ip, 1, charindex('.', @ip) - 1))) + '.' +
replicate('[0-9]', len(substring(@ip, charindex('.', @ip) + 1,
charindex('.', @ip, charindex('.', @ip) + 1) -
charindex('.', @ip) - 1))) + '.' +
replicate('[0-9]', len(reverse(substring(reverse(@ip),
charindex('.', reverse(@ip)) + 1, charindex('.', reverse(@ip),
charindex('.', reverse(@ip)) + 1) -
charindex('.', reverse(@ip)) - 1)))) + '.' +
replicate('[0-9]', len(reverse(substring(reverse(@ip), 1,
charindex('.', reverse(@ip)) - 1))))
) and
-- handles no values in the 1st octet of IP
nullif(replicate('[0-9]', len(substring(@ip, 1, charindex('.', @ip) - 1))), '') is not null And
-- handles no values in the 2nd octet of IP
nullif(replicate('[0-9]', len(substring(@ip, charindex('.', @ip) + 1,
charindex('.', @ip, charindex('.', @ip) + 1) -
charindex('.', @ip) - 1))), '') is not null And
-- handles no values in the 3rd octet of IP
nullif(replicate('[0-9]', len(reverse(substring(reverse(@ip),
charindex('.', reverse(@ip)) + 1,
charindex('.', reverse(@ip), charindex('.', reverse(@ip)) + 1) -
charindex('.', reverse(@ip)) - 1)))), '') is not null And
-- handles no values in the 4th octet of IP
nullif(replicate('[0-9]', len(reverse(substring(reverse(@ip), 1,
charindex('.', reverse(@ip)) - 1)))), '') is not null And
-- checks whether the whole IP value is numeric & also the individual octets
-- the whole value check maybe redundant but the optimizer may save some CPU cycles
isnumeric(replace(@ip, '.', '')) = 1 And
convert(int, substring(@ip, 1, charindex('.', @ip) - 1)) <= 255 and
convert(int, substring(@ip, charindex('.', @ip) + 1,
charindex('.', @ip, charindex('.', @ip) + 1) -
charindex('.', @ip) - 1)) <= 255 and
convert(int, reverse(substring(reverse(@ip),
charindex('.', reverse(@ip)) + 1,
charindex('.', reverse(@ip), charindex('.', reverse(@ip)) + 1) -
charindex('.', reverse(@ip)) - 1))) <= 255 and
convert(int, reverse(substring(reverse(@ip), 1,
charindex('.', reverse(@ip)) - 1))) <= 255
print 'y'
else
print 'n'