Skip to content

Instantly share code, notes, and snippets.

@iaingray

iaingray/convert_ip_to_bigint.sql

Created June 5, 2014 01:12
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save iaingray/0c21b250e5971e238681 to your computer and use it in GitHub Desktop.
Save iaingray/0c21b250e5971e238681 to your computer and use it in GitHub Desktop.
Download ZIP
Convert ip address to bigint in redshift
Raw
convert_ip_to_bigint.sql
( 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 )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment