"Kathy" <r**@roo.com> wrote in message
news:CbydneWBuO8-Y17fRVn-3A@is.co.za...
> Hi All,
>
> I need to know how to do the following
> I need to know how to validate an IP address
> i.e. if you have an ip of 10.1 (not valid)
> if you have ip of 10.1.1.1 (then valid)
>
> How does one do this in SQL
>
> Thanks for the help
> Kathryn
>
>

"JT" <some***@microsoft.com> wrote in message
news:%23pFXjFYfFHA.2152@TK2MSFTNGP14.phx.gbl...
> It may not be bullet proof, but the following first verifies that the IP
> has
> four dots followed or preceeded by at least 1 character. It then verifies
> that all the characters in the IP are either digits or dots.
>
> if '10.1.1.1' like '%_._%._%._%' and not '10.1.1.1' like
> '%[^0123456789.]%'
> print 'Yes'
> else
> print 'No'
>
>
> "Kathy" <r**@roo.com> wrote in message
> news:CbydneWBuO8-Y17fRVn-3A@is.co.za...
>> Hi All,
>>
>> I need to know how to do the following
>> I need to know how to validate an IP address
>> i.e. if you have an ip of 10.1 (not valid)
>> if you have ip of 10.1.1.1 (then valid)
>>
>> How does one do this in SQL
>>
>> Thanks for the help
>> Kathryn
>>
>>
>
>

"Kathy" <r**@roo.com> wrote in message
news:CbydneWBuO8-Y17fRVn-3A@is.co.za...
> Hi All,
>
> I need to know how to do the following
> I need to know how to validate an IP address
> i.e. if you have an ip of 10.1 (not valid)
> if you have ip of 10.1.1.1 (then valid)
>
> How does one do this in SQL
>
> Thanks for the help
> Kathryn
>

"Itzik Ben-Gan" <it***@REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ugDqYfYfFHA.1472@TK2MSFTNGP12.phx.gbl...
> Here you go:
>
> SELECT CASE WHEN
>    -- 3 periods and no empty octets
>    @ip LIKE '%_.%_.%_.%_'
>  AND
>    -- not 4 periods or more
>    @ip NOT LIKE '%.%.%.%.%'
>  AND
>    -- no characters other than digits and periods
>    @ip NOT LIKE '%[^0-9.]%'
>  AND
>    -- not more than 3 digits per octet
>    @ip NOT LIKE '%[0-9][0-9][0-9][0-9]%'
>  AND
>    -- NOT 300 - 999
>    @ip NOT LIKE '%[3-9][0-9][0-9]%'
>  AND
>    -- NOT 260 - 299
>    @ip NOT LIKE '%2[6-9][0-9]%'
>  AND
>    -- NOT 256 - 259
>    @ip NOT LIKE '%25[6-9]%'
>  THEN 'Valid' ELSE 'Invalid' END
>
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
>
> "Kathy" <r**@roo.com> wrote in message
> news:CbydneWBuO8-Y17fRVn-3A@is.co.za...
>> Hi All,
>>
>> I need to know how to do the following
>> I need to know how to validate an IP address
>> i.e. if you have an ip of 10.1 (not valid)
>> if you have ip of 10.1.1.1 (then valid)
>>
>> How does one do this in SQL
>>
>> Thanks for the help
>> Kathryn
>>
>
>

"Kathy" <r**@roo.com> wrote in message
news:c8adnY77SczDRVnfRVn-3w@is.co.za...
> Aaah another genius in our midst
>
> Thanks alot Itzik - brilliant - gonna keep this for future reference
>
> "Itzik Ben-Gan" <it***@REMOVETHIS.SolidQualityLearning.com> wrote in
message
> news:ugDqYfYfFHA.1472@TK2MSFTNGP12.phx.gbl...
> > Here you go:
> >
> > SELECT CASE WHEN
> >    -- 3 periods and no empty octets
> >    @ip LIKE '%_.%_.%_.%_'
> >  AND
> >    -- not 4 periods or more
> >    @ip NOT LIKE '%.%.%.%.%'
> >  AND
> >    -- no characters other than digits and periods
> >    @ip NOT LIKE '%[^0-9.]%'
> >  AND
> >    -- not more than 3 digits per octet
> >    @ip NOT LIKE '%[0-9][0-9][0-9][0-9]%'
> >  AND
> >    -- NOT 300 - 999
> >    @ip NOT LIKE '%[3-9][0-9][0-9]%'
> >  AND
> >    -- NOT 260 - 299
> >    @ip NOT LIKE '%2[6-9][0-9]%'
> >  AND
> >    -- NOT 256 - 259
> >    @ip NOT LIKE '%25[6-9]%'
> >  THEN 'Valid' ELSE 'Invalid' END
> >
> > --
> > BG, SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Kathy" <r**@roo.com> wrote in message
> > news:CbydneWBuO8-Y17fRVn-3A@is.co.za...
> >> Hi All,
> >>
> >> I need to know how to do the following
> >> I need to know how to validate an IP address
> >> i.e. if you have an ip of 10.1 (not valid)
> >> if you have ip of 10.1.1.1 (then valid)
> >>
> >> How does one do this in SQL
> >>
> >> Thanks for the help
> >> Kathryn
> >>
> >
> >
>
>