Thanks John, appreciated!
-----Ursprungligt meddelande----- Från: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] För John Machin Skickat: den 16 mars 2009 00:51 Till: sqlite-users@sqlite.org Ämne: Re: [sqlite] IP from number with SQL On 16/03/2009 8:48 AM, Kees Nuyt wrote: > On Sun, 15 Mar 2009 21:10:02 +0100, "Roger Andersson" > <r...@telia.com> wrote: > >> Hi! >> >> The SQL below might be out there but I didn't find it and since there >> might be other that need to get 32-bit integer IP in a sqlite3 >> database to the a.b.c.d format using SQL >> >> I did get started from >> http://acidlab.sourceforge.net/acid_faq.html#faq_e1 >> and for me what's below does the trick in sqlite3 :-) >> >> SELECT >> CAST((intIP & 4278190080) >> 24 AS text)||'.'|| CAST((intIP & >> 16711680) >> 16 AS text)||'.'|| CAST((intIP & 65280) >> 8 AS >> text)||'.'|| CAST((intIP & 255) AS text) AS strIP FROM IP_table; > > Cute code, thanks. We appear to have differing meanings for "cute" :-) Following are two iterations of make-over: SQLite version 3.6.10 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE IP_table (intIP integer); sqlite> sqlite> INSERT INTO IP_table VALUES(12345678); INSERT INTO IP_table sqlite> VALUES(9876543210123); sqlite> sqlite> SELECT rowid, intIP, ...> CAST((intIP & 4278190080) >> 24 AS text)||'.'|| ...> CAST((intIP & 16711680) >> 16 AS text)||'.'|| ...> CAST((intIP & 65280) >> 8 AS text)||'.'|| ...> CAST((intIP & 255) AS text) AS strIP ...> FROM IP_table; 1|12345678|0.188.97.78 2|9876543210123|143.217.130.139 sqlite> sqlite> SELECT rowid, intIP, ...> CAST((intIP >> 24) & 255 AS text)||'.'|| ...> CAST((intIP >> 16) & 255 AS text)||'.'|| ...> CAST((intIP >> 8) & 255 AS text)||'.'|| ...> CAST((intIP ) & 255 AS text) AS strIP ...> FROM IP_table; 1|12345678|0.188.97.78 2|9876543210123|143.217.130.139 sqlite> sqlite> SELECT rowid, intIP, ...> ((intIP >> 24) & 255) ||'.'|| ...> ((intIP >> 16) & 255) ||'.'|| ...> ((intIP >> 8) & 255) ||'.'|| ...> ((intIP ) & 255) AS strIP ...> FROM IP_table; 1|12345678|0.188.97.78 2|9876543210123|143.217.130.139 sqlite> Cheers, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
