Brakeing an ip address into distinct

[MMIMadness]

I have an access table with a field containing a ip address as a text field. What I’m trying to do is to write a query which creates a separate column for each octet. I’ve sorted the first column out ok (see below) but I’m having problem getting the second, third and forth. Also each octet can be 1, 2 or 3 characters in length. Any ides?

Thanks

Adam

SELECT [ip test].IP, Mid([ip],1,InStr(1,[ip],".")-1) AS octet1
FROM [ip test];

 

[lespaul]

Here's a SQL query that I borrowed from CosmosKramer in a different thread that should do what you want:

SELECT YourTable.IPAddress
FROM YourTable
ORDER BY CInt(Left([IPAddress],InStr([IPAddress],"."))), CInt(Mid([IPAddress],InStr([IPAddress],".")+1,InStr([IPAddress],"."))), CInt(Right([IPAddress],((Len([IPAddress])-(Len(CInt(Left([IPAddress],InStr([IPAddress],"."))))+Len(CInt(Mid([IPAddress],InStr([IPAddress],".")+1,InStr([IPAddress],"."))))+2)))));

HTH



Leslie

 

[OhioSteve]

In this example, the IP address is in myTable.myField.


SELECT myTable.myField,
InStr([myField],".") AS a,
InStr([a]+1,[myField],".") AS b,
Left([myField],[a]-1) AS firstOctet,
Mid([myField],[a]+1,-[a]-1) AS secondOctet,
Mid([myField],+1,3) AS thirdOctet
FROM myTable;

Note that the field "thirdOctet" does not need to always have three characters. It will return accurate values even if the third octet is only one or two digits.

I don't think that Access will force you to display a and b. So I would not show the users this query (a and b would confuse them).

 

[MMIMadness]

Thanks for that guys,

OhioSteve, i took the liberty of modifying your code as the problem was that if the third octet was shorter than 3 digits it would return part of the forth octet. i've also added the forth octet as a returned value as well, acces does want you to show a and b values, but that isn't a issue. revised code below

SELECT [ip test].IP, InStr([ip],".") AS a, InStr([a]+1,[ip],".") AS b, InStr([b]+1,[ip],".") AS c, Left([ip],[a]-1) AS firstOctet, Mid([ip],[a]+1,[b]-[a]-1) AS secondOctet, Mid([ip],[b]+1,[c]-[b]-1) AS thirdOctet, Mid([ip],[c]+1,Len([ip])-[c]) AS ForthOctet
FROM [ip test];

 

[OhioSteve]

Yeah, that's exactly right! I was in a hurry when I wrote the post. I forgot that there are four octets. I also made a typo when I wrote "...I don't think..." I should've said "...I do think..."

Its actually a rather interesting problem intellectually.