How to split an IP Address string in DB2 SQL (for converting to IP Number)?

How can you convert a IP address string into an IP Address number (useful for IP country detection) in DB2 SQL?

Converting to the number is easy, you do something lik开发者_开发知识库e:

16777216 * v + 65536 * x + 256 * y + z

where the IP is in the form v.x.y.z. Plenty of examples, source for this here.

So, the problem becomes, how to parse the IP address string to pull out the components.

In Transact-SQL you can use PARSENAME to do this easily, described here.

How can you do the equivalent in DB2 SQL?

I've not found anything cleaner and simpler than this, but the following SQL will do the job.

SELECT
    LO.IPAddress
    16777216 * CAST(LEFT(LO.IPAddress, LOCATE('.', LO.IPAddress, 1)-1) AS BIGINT)
    +  65536 * CAST(SUBSTR(LO.IPAddress, LOCATE('.', LO.IPAddress, 1) + 1, LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) - LOCATE('.', LO.IPAddress, 1) - 1) AS BIGINT)
    +    256 * CAST(SUBSTR(LO.IPAddress, LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) + 1, LOCATE('.', LO.IPAddress, LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) +1) - LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) - 1) AS BIGINT) 
    +          CAST(RIGHT(LO.IPAddress, LENGTH(LO.IPAddress) - LOCATE('.', LO.IPAddress, LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) +1)) AS BIGINT)
    AS IPNumber

FROM Login LO

Try this out

select IP,(CONVERT(bigint, PARSENAME(IP,1)) + CONVERT(bigint, PARSENAME(IP,2)) * 256 + CONVERT(bigint, PARSENAME(IP,3)) * 65536 + CONVERT(bigint, PARSENAME(IP,4)) * 16777216) from IPTable