如何在sql中用前面的值替换逗号 [英] how to replace comma with preceding value in sql
问题描述
声明@a varchar(20)
set @ a ='1,2,3'
声明@i smallint
set @ i = 1
而 @ i< = 4
开始
选择 @ a = @ mf_schCode 来自 @ tbl ;
set @ qry = ' ,a。' + @ a + ' as' + @ a + ' _ pc'
print @qry
set @ i = @ i + 1
end
输出是
,a.1,2,3为1,2,3_pc
,a .1,2,3为1,2,3_pc
,a.1,2,3为1,2,3_pc
,a.1,2,3 as 1,2,3_pc
但是我想要这样的输出
,a.1 as 1_pc
,a.2为2_pc
,a.3为3_pc
不确定我是否理解正确,但尝试类似
声明 @ qry varchar ( 100 )
声明 @ a varchar ( 20 )
声明 @ i smallint
set @ i = 1
while @ i< = 3
开始
set @ qry = ' ,a。' + cast( @ i as varchar ( 10 ))+ < span class =code-string>'as' + cast( @ i as < span class =code-keyword> varchar ( 10 ))+ ' _ pc'
print @ qry
set @ i = @ i + 1
end
我认为你需要的是一个csv解析器。您可以轻松地为其创建表格函数。你可以在这里找到一个:
将CSV分隔的字符串转换为SQL SERVER中的表列 [ ^ ]
你可以那么将列表用作表格:
声明 @ a varchar (max), @ b nvarchar (max)
set @ b = null
选择 @ a = @ mf_schCode 来自 @ tbl ;
选择 @ b = coalesce ( @b + char ( 10 ),
,a。' + cast(RESULT as nvarchar (max))+
' as' + cast(RESULT as nvarchar (max))+ ' _ pc')
来自 [dbo]。[CSVtoTable] ( @ a )
您可以通过几种方式解决这个问题......请注意,这并非完全如此清楚你桌上的实际内容。
这些都是工作......
1.假设你有一个1,2,3在单独的行中的表
声明 @ tbl table
(
mf_schCode char ( 1 )
)
insert into @ tbl 值
(' 1'),(' 2'),(' 3')
声明 @ qry varchar ( max)
声明 @ a varchar ( 20 )
声明 @ i smallint
set @ i = 1
while @ i< 4
开始
选择 @ a = mf_schCode 来自 @ tbl 其中 mf_schCode = @我
set @ qry = ' ,a。' + @ a + ' as' + @ a + ' _ pc'
print @ qry
set @ i = @ i + 1
end
2.假设你有一个包含'1,2,3'的变量声明 @ a varchar ( 20 )= ' 1,2,3'
声明 @ tbl table (id int identity ( 1 , 1 ),mf_schCode char ( 1 ))
insert into @ tbl 选择 splitdata mf_schCode 来自 dbo.fnSplitString( @ a ,< span class =code-string>' ,')
声明 @ qry varchar (max)
声明 @ i smallint
set @ i = 1
while @ i< 4
开始
选择 @ qry = ' ,a。' + mf_schCode + ' as' + mf_schCode + ' _ pc' 来自 @ tbl 其中 id = @i
print @ qry
set @ i = @ i + 1
end我从中获取了splitString方法 sqlservercentral [ ^ ]
Key要注意的是WHERE子句,否则你每次都会获得最后一个值。
我能得到你发布的结果的唯一方法是在@a中设置一个值(就像你做的那样)和从while语句中删除select。
declare @a varchar(20)
set @a='1,2,3'
declare @i smallint
set @i=1
while @i<=4 begin select @a= @mf_schCode from @tbl; set @qry = ',a.' + @a + ' as ' + @a + '_pc' print @qry set @i=@i+1 end
output is
,a.1,2,3 as 1,2,3_pc
,a.1,2,3 as 1,2,3_pc
,a.1,2,3 as 1,2,3_pc
,a.1,2,3 as 1,2,3_pc
but i want the output like this
,a.1 as 1_pc
,a.2 as 2_pc
,a.3 as 3_pc解决方案Not sure if I understand you correctly, but try something like
declare @qry varchar(100) declare @a varchar(20) declare @i smallint set @i=1 while @i<=3 begin set @qry = ',a.' + cast(@i as varchar(10)) + ' as ' + cast(@i as varchar(10)) + '_pc' print @qry set @i=@i+1 end
I think what you need is a csv parser. You can easily create a tabular function for it. You can find one here:
Convert a CSV delimited string to table column in SQL SERVER[^]
You can then use the list as a table:
declare @a varchar(max), @b nvarchar(max) set @b = null select @a= @mf_schCode from @tbl; select @b= coalesce(@b + char(10), ',a.' + cast(RESULT as nvarchar(max)) + ' as ' + cast(RESULT as nvarchar(max))+ '_pc') from [dbo].[CSVtoTable](@a)
Couple of ways you can address this ... note it's not entirely clear what is actually in your table.
These all work...
1. Assuming you have a table with 1,2,3 in separate rows
declare @tbl table ( mf_schCode char(1) ) insert into @tbl values ('1'),('2'),('3') declare @qry varchar(max) declare @a varchar(20) declare @i smallint set @i=1 while @i<4 begin select @a= mf_schCode from @tbl where mf_schCode = @i set @qry = ',a.' + @a + ' as ' + @a + '_pc' print @qry set @i=@i+1 end
2. Assuming you have a variable containing '1,2,3'declare @a varchar(20) = '1,2,3' declare @tbl table (id int identity(1,1), mf_schCode char(1)) insert into @tbl select splitdata as mf_schCode from dbo.fnSplitString ( @a, ',') declare @qry varchar(max) declare @i smallint set @i=1 while @i<4 begin select @qry = ',a.' + mf_schCode + ' as ' + mf_schCode + '_pc' from @tbl where id = @i print @qry set @i=@i+1 endI got the splitString method from sqlservercentral[^]
Key point to note is the WHERE clause otherwise you will just get the last value each time.
The only way I could get the results you posted was to set a value in @a (as you did) and remove the select from within the while statement.
这篇关于如何在sql中用前面的值替换逗号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!