在 T-SQL 中将 CSV 字符串转换为表的最简洁方法? [英] Most succinct way to transform a CSV string to a table in T-SQL?

问题描述

-- 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'

<小时>

发布这篇文章后，我开始玩一些动态 SQL.这似乎有效，但似乎使用动态 SQL 可能存在一些安全风险 - 对此有何想法?

---

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)

推荐答案

从 这里

但基本上你会:

在您的数据库中创建此函数:

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, ',')

这篇关于在 T-SQL 中将 CSV 字符串转换为表的最简洁方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！