最简单的方法是将CSV字符串转换为TSQL中的表格? [英] Most succinct way to transform a CSV string to an table in TSQL?
本文介绍了最简单的方法是将CSV字符串转换为TSQL中的表格?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
- 给出这样的CSV字符串:
declare @roles varchar(800)
select @roles ='Pub,RegUser,ServiceAdmin '
- 问题:如何使角色进入如下表格视图:
select'Pub'
union
select'RegUser'
union
select'ServiceAdmin'
发布之后,我开始玩一些动态SQL。这似乎工作,但似乎可能有一些安全风险,通过使用动态SQL-在这方面的想法?
declare @rolesSql varchar(800)
select @rolesSql ='select'''+ replace(@roles,',','''union select''')+''''
exec(@rolesSql)
解决方案
//stackoverflow.com/questions/3660734/create-a-table-from-csv-columns-in-sql-server-without-using-a-cursor/3660785#3660785\">在这里
但是基本上你会:在你的DB中创建这个函数:
CREATE FUNCTION dbo.Split(@origString varchar(max),@Delimiter char(1))
返回@temptable TABLE(items varchar(max))
as
begin
declare @idx int
declare @split varchar(max)
select @idx = 1
如果len(@origString)< ; 1 or @origString is null return
while @idx!= 0
begin
set @idx = charindex(@ Delimiter,@ origString)
如果@idx != 0
set @ split = left(@ origString,@ idx - 1)
else
set @ split = @origString
if(len(@split )> 0)
insert into @temptable(Items)values(@split)
set @ origString = right(@ origString,len(@origString) - @idx)
if len(@origString)= 0 break
end
return
end
$ b b
然后调用函数并传入要分割的字符串。
选择* From dbo.Split @roles,',')
-- Given a CSV string like this:
declare @roles varchar(800)
select @roles = 'Pub,RegUser,ServiceAdmin'
-- Question: How to get roles into a table view like this:
select 'Pub'
union
select 'RegUser'
union
select 'ServiceAdmin'
After posting this, I started playing with some dynamic SQL. This seems to work, but seems like there might be some security risks by using dynamic SQL - thoughts on this?
declare @rolesSql varchar(800)
select @rolesSql = 'select ''' + replace(@roles, ',', ''' union select ''') + ''''
exec(@rolesSql)
解决方案
See my answer from here
But basically you would:
Create this function in your DB:
CREATE FUNCTION dbo.Split(@origString varchar(max), @Delimiter char(1))
returns @temptable TABLE (items varchar(max))
as
begin
declare @idx int
declare @split varchar(max)
select @idx = 1
if len(@origString )<1 or @origString is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@origString)
if @idx!=0
set @split= left(@origString,@idx - 1)
else
set @split= @origString
if(len(@split)>0)
insert into @temptable(Items) values(@split)
set @origString= right(@origString,len(@origString) - @idx)
if len(@origString) = 0 break
end
return
end
and then call the function and pass in the string you want to split.
Select * From dbo.Split(@roles, ',')
这篇关于最简单的方法是将CSV字符串转换为TSQL中的表格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文