Extracting IP Address

[Donatello Cruz]

Hi All,

I've got a column in the database that has IP addresses, though the
column data might have multiple IPs, comma separated that take the
form of: 192.168.1.1,192.168.1.3,192.168.1.5

All I want to extract is the first IP address using a CASE statement:

Select
Case When substring(o2.String4, 1, charindex(',', o2.string4)) = ','
Then substring(o2.String4, 1, 12)
Else o2.string4
End as [IP Address]

However, I keep getting all the IPs (when present for each row).

Any help is appreciated!

[Gene Wirchenko]

On Thu, 15 Mar 2012 19:08:08 -0700 (PDT), Donatello Cruz

Post by Donatello Cruz
I've got a column in the database that has IP addresses, though the
column data might have multiple IPs, comma separated that take the
form of: 192.168.1.1,192.168.1.3,192.168.1.5
Select
Case When substring(o2.String4, 1, charindex(',', o2.string4)) = ','

This condition means when the first charindex() return value
characters equal a comma. Not likely so the else applies.

Post by Donatello Cruz
Then substring(o2.String4, 1, 12)
Else o2.string4
End as [IP Address]

declare @IPString varchar(max)='192.168.1.1,192.168.1.3,192.168.1.5';

select
case
when charindex(',',@IPString)>0
then left(@IPString,charindex(',',@IPString)-1)
else @IPString
end as IPAddress

Mine means:
when @IPString contains a comma
then all characters before the comma
else the whole string

Post by Donatello Cruz
However, I keep getting all the IPs (when present for each row).

Because your condition is wonky.

Post by Donatello Cruz
Any help is appreciated!

You are welcome.

Sincerely,

Gene Wirchenko

[George Neuner]

On Thu, 15 Mar 2012 19:08:08 -0700 (PDT), Donatello Cruz

Post by Donatello Cruz
I've got a column in the database that has IP addresses, though the
column data might have multiple IPs, comma separated that take the
form of: 192.168.1.1,192.168.1.3,192.168.1.5
Select
Case When substring(o2.String4, 1, charindex(',', o2.string4)) = ','
Then substring(o2.String4, 1, 12)
Else o2.string4
End as [IP Address]
However, I keep getting all the IPs (when present for each row).
Any help is appreciated!

A general solution needs to know the location of the comma ... an IP
address may be anywhere from 7 to 15 characters.

select case
when charindex(',' , address ) > 0
then left( address, charindex(',' , address )-1 )
else address
end;

Downside is charindex() might be evaluated twice unless you break it
into 2 statements: e.g.,

set @loc = charindex(',' , address );
select case
when @loc > 0
then left( address, @loc - 1 )
else address
end;

I'm not sure if this can be written in a single statement such that
charindex() is evaluated only once. Anyone?

George

[Donatello Cruz]

Thanks for the help guys. I do appreciate it.