SQL 语句中解析IP地址

最新推荐文章于 2024-09-20 11:33:48 发布

平淡从容

最新推荐文章于 2024-09-20 11:33:48 发布

阅读量1k

点赞数

分类专栏： db 文章标签： sql numbers parsing function table database

db 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

parse ip in sql

http://microcodes.blogspot.com/2008/06/parsing-ip-address-in-sql-server.html

需要翻墙才能查看，故记录在此。

另外通过测试，mysql用户修改如下，可以方便执行（不依赖任何db和table）。

select substring_index(substring_index(y.ip,'.',1),'.',-1) a,

substring_index(substring_index(y.ip,'.',2),'.',-1) b,

substring_index(substring_index(y.ip,'.',3),'.',-1) c,

substring_index(substring_index(y.ip,'.',4),'.',-1) d

from (select '92.111.0.2' as ip) y

下面是统计电信用户分布的一些sql语句

1）统计ip段前三部分相同的ip段及出现的次数。

select substring_index(gameIp,'.',3) as ipSeg,count(*) as ipCounts

from ut2.play_log where gameTime>'2012-07-10' and length(playerName)=8 group by ipSeg order by ipSeg;

2）统计玩家使用过的所有ip，以及这些ip的出现次数。可以看出哪些地方的玩家更活跃。

select gameIp,count(*) ipCounts

from ut2.play_log where gameTime>'2012-07-10' and length(playerName)=8 group by gameIp order by gameIp;

3）统计玩家玩牌的次数，以及该玩家的ip（因为合并后，可能只是该玩家曾经玩过的其中某一个ip）。

select playerName,gameIp,count(*) as playCounts from ut2.play_log

where gameTime>'2012-07-10' and length(playerName)=8 group by playerName order by gameip;

以下是原文：

Parsing an IP Address in SQL Server

Problem

You want to parse an IP address's fields into columns. Consider the following IP address:

 111.22.3.4

You would like the result of your query to be:

 A     B    C      D
 ----- ----- ----- ---
 111   22    3     4

Solution

The solution depends on the built-in functions provided by your DBMS. Regardless of your DBMS, being able to locate periods and the numbers immediately surrounding them are the keys to the solution.

DB2

Use the recursive WITH clause to simulate an iteration through the IP address while using SUBSTR to easily parse it. A leading period is added to the IP address so that every set of numbers has a period in front of it and can be treated the same way.

  1 with x (pos,ip) as (
  2   values (1,'.92.111.0.222')
  3   union all
  4  select pos+1,ip from x where pos+1 <= 20
  5 )
  6 select max(case when rn=1 then e end) a,
  7        max(case when rn=2 then e end) b,
  8        max(case when rn=3 then e end) c,
  9        max(case when rn=4 then e end) d
 10   from (
 11 select pos,c,d,
 12        case when posstr(d,'.') > 0 then substr(d,1,posstr(d,'.')-1)
 13             else d
 14        end as e,
 15        row_number() over( order by pos desc) rn
 16   from (
 17 select pos, ip,right(ip,pos) as c, substr(right(ip,pos),2) as d
 18   from x
 19  where pos <= length(ip)
 20   and substr(right(ip,pos),1,1) = '.'
 21      ) x
 22      ) y

MySQL

The function SUBSTR_INDEX makes parsing an IP address an easy operation:

 1 select substring_index(substring_index(y.ip,'.',1),'.',-1) a,
 2        substring_index(substring_index(y.ip,'.',2),'.',-1) b,
 3        substring_index(substring_index(y.ip,'.',3),'.',-1) c,
 4        substring_index(substring_index(y.ip,'.',4),'.',-1) d
 5   from (select '92.111.0.2' as ip from t1) y

Oracle

Use the built-in function SUBSTR and INSTR to parse and navigate through the IP address:

 1 select ip,
 2        substr(ip, 1, instr(ip,'.')-1 ) a,
 3        substr(ip, instr(ip,'.')+1,
 4                    instr(ip,'.',1,2)-instr(ip,'.')-1 ) b,
 5        substr(ip, instr(ip,'.',1,2)+1,
 6                    instr(ip,'.',1,3)-instr(ip,'.',1,2)-1 ) c,
 7        substr(ip, instr(ip,'.',1,3)+1 ) d
 8   from (select '92.111.0.2' as ip from t1)

PostgreSQL

Use the built-in function SPLIT_PART to parse an IP address:

 1 select split_part(y.ip,'.',1) as a,
 2        split_part(y.ip,'.',2) as b,
 3        split_part(y.ip,'.',3) as c,
 4        split_part(y.ip,'.',4) as d
 5   from (select cast('92.111.0.2' as text) as ip from t1) as y

SQL Server

  1  with x (pos,ip) as (
  2    select 1 as pos,'.92.111.0.222' as ip from t1
  3    union all
  4   select pos+1,ip from x where pos+1 <= 20
  5  )
  6  select max(case when rn=1 then e end) a,
  7         max(case when rn=2 then e end) b,
  8         max(case when rn=3 then e end) c,
  9         max(case when rn=4 then e end) d
 10    from  (
 11  select pos,c,d,
 12         case when charindex('.',d) > 0
 13            then substring(d,1,charindex('.',d)-1)
 14            else d
 15        end as e,
 16        row_number() over(order by pos desc) rn
 17   from (
 18 select pos, ip,right(ip,pos) as c,
 19        substring(right(ip,pos),2,len(ip)) as d
 20   from x
 21  where pos <= len(ip)
 22    and substring(right(ip,pos),1,1) = '.'
 23       ) x
 24       ) y

Discussion

By using the built-in functions for your database, you can easily walk through parts of a string. The key is being able to locate each of the periods in the address. Then you can parse the numbers between each.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

1 Comment:

Anonymous said...

*or this

-- SQL Server
SELECT
CAST( PARSENAME( IP , 4 ) AS int ) AS A ,
CAST( PARSENAME( IP , 3 ) AS int ) AS B ,
CAST( PARSENAME( IP , 2 ) AS int ) AS C ,
CAST( PARSENAME( IP , 1 ) AS int ) AS D
FROM
( SELECT '92.111.0.222' AS IP ) T

July 8, 2010 9:58 PM