mysql ip地址 分割_sql 语句系列(分割ip)[八百章之第十四章]

前言

单独列出的章节。因为用处比较多。

mysql

select SUBSTRING_INDEX(SUBSTRING_INDEX("192.168.1.1",".",1),'.',-1),

SUBSTRING_INDEX(SUBSTRING_INDEX("192.168.1.1",".",2),'.',-1),

SUBSTRING_INDEX(SUBSTRING_INDEX("192.168.1.1",".",3),'.',-1),

SUBSTRING_INDEX(SUBSTRING_INDEX("192.168.1.1",".",4),'.',-1)

from T1

这个无需解释。

sql service

是我第上一章最后一个例子的扩展

select max(case when y.rn=1 then addressSub end) a,

max(case when y.rn=2 then addressSub end) b,

max(case when y.rn=3 then addressSub end) c,

max(case when y.rn=4 then addressSub end) d

from

(

select ROW_NUMBER() over(partition by name order by len(c) desc) rn,c,SUBSTRING(c,2,CHARINDEX(',',c,2)-2) as addressSub

from

(

select SUBSTRING(csv.emps,iter.pos,LEN(csv.emps)) c,'7654,7698,7782,7788' name

from (select ','+'7654,7698,7782,7788'+','as emps from t1) csv,(select id as pos from t100) iter

where iter.pos

) x

where LEN(c)>1 and SUBSTRING(c,1,1)=','

) y