[font="Verdana"]Here's some example code to give you an idea:

declare @IPAddress table(AddressID int not null primary key, IPAddress varchar(15) not null);

insert into @IPAddress

select 101, '8.143.46.' union all

select 102, '8.243.6.AC' union all

select 103, '192.168.1.1' union all

select 104, '192.BC.168.1.1' union all

select 105, '192.212.456.21' union all

select 106, '1.2.3.4.5.6'

;

with

MarkedAddress as (

selectAddressID,

IPAddress,

0 as Marker0,

charindex('.', IPAddress, 1) as Marker1,

charindex('.', IPAddress, charindex('.', IPAddress, 1) + 1) as Marker2,

charindex('.', IPAddress, charindex('.', IPAddress, charindex('.', IPAddress, 1) + 1) + 1) as Marker3,

len(IPAddress) + 1 as Marker4

from@IPAddress

),

PartionedAddress as (

selectAddressID,

IPAddress,

substring(IPAddress, Marker0 + 1, Marker1 - Marker0 - 1) as Part1,

substring(IPAddress, Marker1 + 1, Marker2 - Marker1 - 1) as Part2,

substring(IPAddress, Marker2 + 1, Marker3 - Marker2 - 1) as Part3,

substring(IPAddress, Marker3 + 1, Marker4 - Marker3 - 1) as Part4

fromMarkedAddress

)

select*,

case

when IPAddress like '%[^0-9.]%' then 0

when IPAddress like '%.%.%.%.%' then 0

when IPAddress not like '[0-9]%.[0-9]%.[0-9]%.[0-9]%' then 0

when cast(Part1 as bigint) > 255 then 0

when cast(Part2 as bigint) > 255 then 0

when cast(Part3 as bigint) > 255 then 0

when cast(Part4 as bigint) > 255 then 0

else 1

end as IsAddressValid

fromPartionedAddress

;

Edited because it didn't need the check for length zero parts as the parttern check will pick up those, and it needed an additional check if there's too many full stops in the pattern.

[/font]