如果只是解析IP,3个点号。 可使用parsename()create table #tb(ip varchar(15)) insert into #tb values('192.168.12.1') insert into #tb values('192.168.12.2') insert into #tb values('192.168.12.3') insert into #tb values('192.168.12.4') insert into #tb values('192.168.12.15') goselect parsename(ip,4)+'.'+parsename(ip,3)+'.'+parsename(ip,2),parsename(ip,1) from #tbselect replace(ip,'.'+parsename(ip,1),''),parsename(ip,1) from #tb
,
reverse(left(reverse(ip),charindex(reverse(ip),'.')-1)) from tb
insert into tb values('192.168.12.1')
insert into tb values('192.168.12.2')
insert into tb values('192.168.12.3')
insert into tb values('192.168.12.4')
insert into tb values('192.168.12.15')
goselect reverse(substring(reverse(ip),charindex('.',reverse(ip))+1,len(ip))),
reverse(left(reverse(ip),charindex('.',reverse(ip))-1))
from tbdrop table tb/*
--------------- ---------------
192.168.12 1
192.168.12 2
192.168.12 3
192.168.12 4
192.168.12 15(所影响的行数为 5 行)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-08 23:37:15
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(12))
insert [tb]
select '192.168.12.1'
--------------开始查询--------------------------
select
reverse(substring(reverse(col),charindex('.',reverse(col))+1,len(col))),parsename(col,1)
from
tb
----------------结果----------------------------
/*
------------ --------------------------------------------------------------------------------------------------------------------------------
192.168.12 1(1 行受影响)
*/
set @c='192.168.1.2'
select left(@c,charindex('.',@c,charindex('.',@c,charindex('.',@c)+1)+1)-1),right(@c,len(@c)-charindex('.',@c,charindex('.',@c,charindex('.',@c)+1)+1))
/*
-------------------- --------------------
192.168.1 2*/
可使用parsename()create table #tb(ip varchar(15))
insert into #tb values('192.168.12.1')
insert into #tb values('192.168.12.2')
insert into #tb values('192.168.12.3')
insert into #tb values('192.168.12.4')
insert into #tb values('192.168.12.15')
goselect parsename(ip,4)+'.'+parsename(ip,3)+'.'+parsename(ip,2),parsename(ip,1)
from #tbselect replace(ip,'.'+parsename(ip,1),''),parsename(ip,1) from #tb