如何使用SQL截取空格字符串
路径顺序号 路径
A1 A2 A3
B1 B2 B3
如何使用SQL的存储过程和游标将它修改成为
路径顺序号 路径
1 A1
2 A2
3 A3
1 B1
2 B2
3 B3
[最优解释]
create table tb(col varchar(20))
insert into tb
select 'A1 A2 A3' union all
select 'B1 B2 B3'
--创建表值函数
create function f_name(@str varchar(20))
returns @tb table(no int Identity(1,1),col varchar(10))
as
begin
select @str=replace(@str,' ',',')+','
while charindex(',',@str)>1
begin
insert into @tb (col)
select left(@str,charindex(',',@str)-1)
set @str=right(@str,len(@str)-charindex(',',@str))
end
return
end
--查询
select b.* from tb cross apply f_name(tb.col) b
/*
no col
----------- ----------
1 A1
2 A2
3 A3
1 B1
2 B2
3 B3
(6 row(s) affected)
--> 测试数据
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([路径] varchar(20))
insert [TB]
select 'A1 A2 A3' union all
select 'B1 B2 B3'
SELECT
[路径编号]=RIGHT(flag,1),
[路径]=left(flag,1) FROM(
select
flag=PARSENAME(REPLACE([路径],' ','.'),3)
from [TB]
UNION ALL
select
PARSENAME(REPLACE([路径],' ','.'),2)
from [TB]
UNION ALL
select
PARSENAME(REPLACE([路径],' ','.'),1)
from [TB]
)t
ORDER BY 2,1
/*
路径编号 路径
---- ----
1 A
2 A
3 A
1 B
2 B
3 B
(6 行受影响)
*/
DROP TABLE TB
declare @t table (
路径顺序号 int,
路径 varchar(30)
)
insert @t select 1,'A1 A2 A3'
union all select 2,'B1 B2 B3'
declare @s varchar(3000)
declare @i int
select @s = ISNULL(@s + ' union all select '+ CAST(路径顺序号 as varchar) +',''','
declare @i int
declare @j int
declare @t table (
路径顺序号 int,
路径 varchar(30),
i int
)
insert @t(i,路径)
select '+ CAST(路径顺序号 as varchar) +',''') + REPLACE(路径,' ',''' union all select '+ CAST(路径顺序号 as varchar) +',''') + '''' from @t
set @s = @s + '
update @t set
@j = case when @i = i then @j + 1 else 1 end
,@i = i
,路径顺序号 = @j
select 路径顺序号,路径 from @t
'
exec( @s)
--结果
路径顺序号路径
1A1
2A2
3A3
1B1
2B2
3B3
