This Question have 2 answers right now.

How to Convert userip to integer in AMAZON REDSHIFT

Question!

just starting to play around and test amazon's redshift. One thing I need to do that i can easily do in sql is change userip to integer. This is done in mssql with a scalar function that uses parsename to break up the ip numbres and multiple by them by constants.

 CAST(

       (CAST(PARSENAME(@IP,4) AS BIGINT) * 16777216) +
       (CAST(PARSENAME(@IP,3) AS BIGINT) * 65536) +
       (CAST(PARSENAME(@IP,2) AS BIGINT) * 256) +
        CAST(PARSENAME(@IP,1) AS BIGINT) 
  AS BIGINT)

That is what it looks likes for reference.

As i expected parsename is not a function in redshift and thus my question arises. Do you guys know of a way i can achieve the same resuslt?

Figured it out:

( LEFT(ip_address, STRPOS(ip_address, '.')-1) * 16777216) + (LEFT(SUBSTRING(ip_address, LEN(LEFT(ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2), STRPOS( SUBSTRING(ip_address, LEN(LEFT(ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2), '.')-1) * 65536) + (RIGHT( SUBSTRING(ip_address, LEN(LEFT(ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2), LEN(SUBSTRING(ip_address, LEN(LEFT(ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2)) - STRPOS(SUBSTRING(ip_address, LEN(LEFT(ip_address, STRPOS(ip_address, '.')+1)), LEN(ip_address) - LEN(LEFT(ip_address, STRPOS(ip_address, '.')-1)) - LEN(LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1)) - 2), '.') ) * 256) + (REVERSE( LEFT(REVERSE(ip_address), STRPOS(REVERSE(ip_address), '.')-1) ) * 1 )



Answers
Wow, my eyes are watering at the sight of that query, though I'm sure you don't have tons of choice given the restrictions imposed by Redshift.

I'm still amazed you have to do something quite that cumbersome. Can't you at least create an SQL function or two to tidy it up? Or does Redshift not even support CREATE FUNCTION ... LANGUAGE sql?

For reference, in proper PostgreSQL you'd do:

select (split_part(ip, '.', 1)::bigint << 24) +
       (split_part(ip, '.', 2)::bigint << 16) +
       (split_part(ip, '.', 3)::bigint << 8) +
       (split_part(ip, '.', 4)::bigint);

or using a simple-ish SQL function:

CREATE OR REPLACE FUNCTION inet_to_bigint(inet) AS $$
SELECT sum(split_part($1::text,'.',octetnum)::bigint << (32 - octetnum*8))
FROM generate_series(1,4) octetnum;
$$ LANGUAGE sql;

or, almost certainly most efficiently, by abusing the inet data type's subtraction operator:

SELECT (ip - '0.0.0.0')

(This one might even work in Redshift if they've retained the inet data type and if this feature existed back in PostgreSQL 8.1, when ParAccel forked from PostgreSQL).

On a side note, I was quite astonished to see that there's no cast defined from inet to bigint, in PostgreSQL, as I expected to just be able to write '127.0.0.1'::inet::bigint, which would be shorthand for CAST(CAST('127.0.0.1' AS inet) AS bigint).



split_part(ip, '.', n) should do it.



Video about How to Convert userip to integer in AMAZON REDSHIFT