create
table
#
temp
(
id int identity ( 1 , 1 ),
zise nvarchar ( 200 )
)
declare @zise nvarchar ( 200 )
declare @n int
declare @rows int
select @n = 1
insert # temp (zise) select prosize from tt
select @rows = @@rowcount
while @n <= @rows
begin
select @zise = prosize
from tt
where prosize = ( select zise from # temp where id = @n )
-- 对字段里面不规则空格进行化为一个空格
set @zise = ltrim ( @zise )
set @zise = rtrim ( @zise )
while charindex ( ' ' , @zise ) <> 0
begin
set @zise = replace ( @zise , ' ' , ' ' )
end
-- insert into #temp(zise) values (@zise) --原临时表追加插入
update # temp set zise = @zise where id = @n -- 更新
-- print(@zise)
select @n = @n + 1
end
select * from # temp
drop TABLE # temp
/*
结果:
44 1975*990*110 1740*1010*100
45 1975*990*110 1740*1010*100
46 1975*990*110 1740*1010*100
47 1010*920*190 1940*1010*110
48 1010*920*190 1940*1010*110
****
变成:
27 1010*920*190 1940*1010*110
28 1010*920*190 1940*1010*110
29 1010*920*190 1940*1010*110
30 1010*920*190 1940*1010*110
*/
/*
字段(size):
1 aa
2 bb cc
*/
-- 可以通过 因为 bb cc 之间有空格
select LEFT (zise, CHARINDEX ( ' ' ,zise) - 1 ) AS A from # temp where id = 2
select LEFT (zise, CHARINDEX ( ' ' ,zise) - 1 ) AS A from # temp
-- 传递到 SUBSTRING 函数的长度参数无效。因为有些行没有空格 如第一行
-- 解决:
-- 取第一个
select LEFT (zise, CHARINDEX ( ' ' ,zise + ' ' ) - 1 ) AS A from # temp
-- 取第二个 中间不规则空格
select ltrim ( rtrim ( substring (zise, CHARINDEX ( ' ' ,zise + ' ' ), len (zise + ' ' ) - CHARINDEX ( ' ' ,zise) + 1 ))) as A from # temp
select substring (zise, CHARINDEX ( ' ' ,zise + ' ' ), len (zise + ' ' ) - CHARINDEX ( ' ' ,zise) + 1 ) as A from # temp
-- 临时表 + While循环 对列进行遍历
create table # temp
(
id int identity ( 1 , 1 ),
zise nvarchar ( 50 )
)
declare @zise nvarchar ( 50 )
declare @n int
declare @rows int
select @n = 1
insert # temp (zise) select prosize from tt
select @rows = @@rowcount
while @n <= @rows
begin
select @zise = prosize
from tt
where prosize = ( select zise from # temp where id = @n )
print ( @zise )
select @n = @n + 1
end
drop TABLE # temp
/*
结果:
(169 行受影响)
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
.
*/
-- 游标(Cursor) + While循环 对列进行遍历
declare @size nvarchar ( 50 )
declare pcurr cursor for
select prosize from tt
open pcurr
fetch next from pcurr into @size
while ( @@fetch_status = 0 )
begin
print ( @size )
fetch next from pcurr into @size
end
close pcurr
deallocate pcurr
/*
结果:
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
.
(
id int identity ( 1 , 1 ),
zise nvarchar ( 200 )
)
declare @zise nvarchar ( 200 )
declare @n int
declare @rows int
select @n = 1
insert # temp (zise) select prosize from tt
select @rows = @@rowcount
while @n <= @rows
begin
select @zise = prosize
from tt
where prosize = ( select zise from # temp where id = @n )
-- 对字段里面不规则空格进行化为一个空格
set @zise = ltrim ( @zise )
set @zise = rtrim ( @zise )
while charindex ( ' ' , @zise ) <> 0
begin
set @zise = replace ( @zise , ' ' , ' ' )
end
-- insert into #temp(zise) values (@zise) --原临时表追加插入
update # temp set zise = @zise where id = @n -- 更新
-- print(@zise)
select @n = @n + 1
end
select * from # temp
drop TABLE # temp
/*
结果:
44 1975*990*110 1740*1010*100
45 1975*990*110 1740*1010*100
46 1975*990*110 1740*1010*100
47 1010*920*190 1940*1010*110
48 1010*920*190 1940*1010*110
****
变成:
27 1010*920*190 1940*1010*110
28 1010*920*190 1940*1010*110
29 1010*920*190 1940*1010*110
30 1010*920*190 1940*1010*110
*/
/*
字段(size):
1 aa
2 bb cc
*/
-- 可以通过 因为 bb cc 之间有空格
select LEFT (zise, CHARINDEX ( ' ' ,zise) - 1 ) AS A from # temp where id = 2
select LEFT (zise, CHARINDEX ( ' ' ,zise) - 1 ) AS A from # temp
-- 传递到 SUBSTRING 函数的长度参数无效。因为有些行没有空格 如第一行
-- 解决:
-- 取第一个
select LEFT (zise, CHARINDEX ( ' ' ,zise + ' ' ) - 1 ) AS A from # temp
-- 取第二个 中间不规则空格
select ltrim ( rtrim ( substring (zise, CHARINDEX ( ' ' ,zise + ' ' ), len (zise + ' ' ) - CHARINDEX ( ' ' ,zise) + 1 ))) as A from # temp
select substring (zise, CHARINDEX ( ' ' ,zise + ' ' ), len (zise + ' ' ) - CHARINDEX ( ' ' ,zise) + 1 ) as A from # temp
-- 临时表 + While循环 对列进行遍历
create table # temp
(
id int identity ( 1 , 1 ),
zise nvarchar ( 50 )
)
declare @zise nvarchar ( 50 )
declare @n int
declare @rows int
select @n = 1
insert # temp (zise) select prosize from tt
select @rows = @@rowcount
while @n <= @rows
begin
select @zise = prosize
from tt
where prosize = ( select zise from # temp where id = @n )
print ( @zise )
select @n = @n + 1
end
drop TABLE # temp
/*
结果:
(169 行受影响)
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
![](https://www.cnblogs.com/Images/dot.gif)
*/
-- 游标(Cursor) + While循环 对列进行遍历
declare @size nvarchar ( 50 )
declare pcurr cursor for
select prosize from tt
open pcurr
fetch next from pcurr into @size
while ( @@fetch_status = 0 )
begin
print ( @size )
fetch next from pcurr into @size
end
close pcurr
deallocate pcurr
/*
结果:
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
1940*1050*135
![](https://www.cnblogs.com/Images/dot.gif)
*/
本文转自曾祥展博客园博客,原文链接:http://www.cnblogs.com/zengxiangzhan/archive/2009/11/05/1596486.html,如需转载请自行联系原作者