0.0.0.0 0.255.255.255 IANA
1.0.0.0 1.51.255.255 IANA
1.52.0.0 1.52.255.255 美国
1.53.0.0 1.153.255.255 IANA
1.154.0.0 1.154.255.255 美国
1.155.0.0 1.255.255.255 IANA
2.0.0.0 2.255.255.255 IANA
3.0.0.0 3.255.255.255 美国 康涅狄格州费尔菲尔德县费尔菲尔德镇通用电气公司上面是一个字段 空格不规则,怎么让他分成3个字段,如字段1:0.0.0.0 字段2:0.255.255.255
字段3:IANA 有什么好方法?用sql怎么实现?
1.0.0.0 1.51.255.255 IANA
1.52.0.0 1.52.255.255 美国
1.53.0.0 1.153.255.255 IANA
1.154.0.0 1.154.255.255 美国
1.155.0.0 1.255.255.255 IANA
2.0.0.0 2.255.255.255 IANA
3.0.0.0 3.255.255.255 美国 康涅狄格州费尔菲尔德县费尔菲尔德镇通用电气公司上面是一个字段 空格不规则,怎么让他分成3个字段,如字段1:0.0.0.0 字段2:0.255.255.255
字段3:IANA 有什么好方法?用sql怎么实现?
LEFT(data,CHARINDEX(' ',data)-1) AS ip1,
LEFT(LTRIM(STUFF(data,1,CHARINDEX(' ',data),'')),
CHARINDEX(' ',LTRIM(STUFF(data,1,CHARINDEX(' ',data),'')))-1) AS ip2,
LTRIM(SUBSTRING(data,PATINDEX('%[^0-9. ]%',data),8000)) AS address
FROM tb
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-25 11:26:18.607●●●●●
★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(id int identity(1,1),name nvarchar(1000))
insert tb(name)
select
'0.0.0.0 0.255.255.255 IANA' union all select
'1.0.0.0 1.51.255.255 IANA' union all select
'1.52.0.0 1.52.255.255 美国' union all select
'1.53.0.0 1.153.255.255 IANA' union all select
'1.154.0.0 1.154.255.255 美国' union all select
'1.155.0.0 1.255.255.255 IANA' union all select
'2.0.0.0 2.255.255.255 IANA' union all select
'3.0.0.0 3.255.255.255 美国-康涅狄格州费尔菲尔德县费尔菲尔德镇通用电气公司'
drop table #a
select * into #a from tb
declare @sql nvarchar(4000)
declare @i int
set @i=0
while @@rowcount>0
begin
select @i=@i+1,@sql=N'alter table #a add name'+cast(@i as nvarchar)+N' nvarchar(100)'
exec(@sql)
set @sql=N'update a set name'+cast(@i as nvarchar)+N'
=left(a.name,charindex('' '',a.name+'' '')-1),
a.name=stuff(a.name,1,charindex('' '',a.name+'' ''),'''')
from #a a where a.name>'''''
exec(@sql)
end
go
--删除分拆后的空列
exec('alter table #a drop column name,name4,name5')
go
select * from #a
drop table #a/*
id name1 name2 name3
1 0.0.0.0 0.255.255.255 IANA
2 1.0.0.0 1.51.255.255 IANA
3 1.52.0.0 1.52.255.255 美国
4 1.53.0.0 1.153.255.255
5 1.154.0.0 1.154.255.255 美国
6 1.155.0.0 1.255.255.255 IANA
7 2.0.0.0 2.255.255.255 IANA
8 3.0.0.0 3.255.255.255 美国-康涅狄格州费尔菲尔德县费尔菲尔德镇通用电气公司
*/
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-25 11:26:18.607●●●●●
★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(id int identity(1,1),name nvarchar(1000))
insert tb(name)
select
'0.0.0.0 0.255.255.255 IANA' union all select
'1.0.0.0 1.51.255.255 IANA' union all select
'1.52.0.0 1.52.255.255 美国' union all select
'1.53.0.0 1.153.255.255 IANA' union all select
'1.154.0.0 1.154.255.255 美国' union all select
'1.155.0.0 1.255.255.255 IANA' union all select
'2.0.0.0 2.255.255.255 IANA' union all select
'3.0.0.0 3.255.255.255 美国-康涅狄格州费尔菲尔德县费尔菲尔德镇通用电气公司'
drop table #a
select * into #a from tb
declare @sql nvarchar(4000)
declare @i int
set @i=0
while @@rowcount>0
begin
select @i=@i+1,@sql=N'alter table #a add name'+cast(@i as nvarchar)+N' nvarchar(100)'
exec(@sql)
set @sql=N'update a set name'+cast(@i as nvarchar)+N'
=left(a.name,charindex('' '',a.name+'' '')-1),
a.name=stuff(a.name,1,charindex('' '',a.name+'' ''),'''')
from #a a where a.name>'''''
exec(@sql)
end
go
--删除分拆后的空列
exec('alter table #a drop column name,name4')
go
select * from #a
drop table #a/*
id name1 name2 name3
1 0.0.0.0 0.255.255.255 IANA
2 1.0.0.0 1.51.255.255 IANA
3 1.52.0.0 1.52.255.255 美国
4 1.53.0.0 1.153.255.255 IANA
5 1.154.0.0 1.154.255.255 美国
6 1.155.0.0 1.255.255.255 IANA
7 2.0.0.0 2.255.255.255 IANA
8 3.0.0.0 3.255.255.255 美国-康涅狄格州费尔菲尔德县费尔菲尔德镇通用电气公司
*/