declare @ip varchar(15)
select @ip = '2.1.232.22' 
-- Values to check:  '2.1.232.22' '2....' '...' '.1.2.' '.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'

This page was last updated on May 01, 2006 04:28 PM.