query to break data apart

[thadson]

Hi.

I have a table that has 1 field called IP
it containes IP addresses as data like this.

IP
255.254.253.252
255.251.250.0

Is it possible to have a query that would break this up so it becomes a new table with 4 fields like this:

IP1 IP2 IP3 IP4
255 254 253 252
255 251 250 0

I'm a beginer in this and I could not find a way so for to break this apart correctly.

Please help.

 

[EMP]

SELECT
Left([IP],InStr([IP],".")-1) AS IP1,
Left(Mid([IP],Len([IP1])+2),InStr(Mid([IP],Len([IP1])+2),".")-1) AS IP2,
Left(Mid([IP],Len([IP1])+Len([IP2])+3),InStr(Mid([IP],Len([IP1])+Len([IP2])+3),".")-1) AS IP3,
Mid([IP],Len([IP1])+Len([IP2])+Len([IP3])+4) AS IP4
FROM [TableName];

~

 

[thadson]

WOW, Thank you.

 

[thadson]

i need a little more help.

you formula is really great. I used it for 2 weeks and it works perfect for the most parts.

I have noticed only 1 problem.

when I have a partial IP address like

255.255.255.
or
255.255.

I start getting error messagesin the query.

IP1 IP2 IP3 IP4
255 255 255 255
255 255 255
255 255 #Error #Error

sometimes I need to have only the first to part of an IP in the list.

Is there any way to acomplish this without messing up and already very good query?
Thank you.