将 CSV 字符串转换为 T-SQL 中的表的最简洁方法？

Question

-- 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 似乎可能存在一些安全风险 -对此有什么想法吗？

declare @rolesSql varchar(800)
select  @rolesSql = 'select ''' + replace(@roles, ',', ''' union select ''') + ''''
exec(@rolesSql)

Answer 1

如果您使用 SQL Server 兼容性级别 130，那么 STRING_SPLIT 函数现在是最简洁的可用方法。

参考链接：https://msdn.microsoft.com/en-gb/library/ mt684588.aspx

用法：

SELECT * FROM string_split('Pub,RegUser,ServiceAdmin',',')

RESULT:

value
-----------
Pub
RegUser
ServiceAdmin

Answer 2

请参阅我的回答 这里

但基本上您会：

在数据库中创建此函数：

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

然后调用该函数并传入要拆分的字符串。

Select * From dbo.Split(@roles, ',')

Answer 3

以下是对您的选项的全面讨论：

• SQL Server 中的数组和列表

Answer 4

在这种情况下我所做的只是使用一些字符串替换将其转换为 json 并像表一样打开 json。可能并不适合所有用例，但它的运行非常简单并且可以使用字符串和文件。对于文件，您只需要观察换行符，大多数情况下我发现它是“Char(13)+Char(10)”

declare @myCSV nvarchar(MAX)= N'"Id";"Duration";"PosX";"PosY"
"•P001";223;-30;35
"•P002";248;-28;35
"•P003";235;-26;35'

--CSV to JSON
    --convert to json by replacing some stuff
    declare @myJson nvarchar(MAX)= '[['+  replace(@myCSV, Char(13)+Char(10), '],[' )  +']]'
        set @myJson = replace(@myJson, ';',',')         -- Optional: ensure coma delimiters for json if the current delimiter differs
    --  set @myJson = replace(@myJson, ',,',',null,')   -- Optional: empty in between
    --  set @myJson = replace(@myJson, ',]',',null]')   -- Optional: empty before linebreak
    
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 0))-1 AS LineNumber, *
    FROM   OPENJSON( @myJson ) 
    with (
         col0   varchar(255)    '$[0]'
        ,col1   varchar(255)    '$[1]'
        ,col2   varchar(255)    '$[2]'
        ,col3   varchar(255)    '$[3]'
        ,col4   varchar(255)    '$[4]'
        ,col5   varchar(255)    '$[5]'
        ,col6   varchar(255)    '$[6]'
        ,col7   varchar(255)    '$[7]'
        ,col8   varchar(255)    '$[8]'  
        ,col9   varchar(255)    '$[9]'
        --any name column count is possible
    ) csv
    order by (SELECT 0) OFFSET 1 ROWS --hide header row

Answer 5

使用 SQL Server 内置的 XML 解析也是一种选择。当然，这掩盖了符合 RFC-4180 的 CSV 的所有细微差别。

-- Given a CSV string like this:
declare @roles varchar(800)
select  @roles = 'Pub,RegUser,ServiceAdmin'

-- Here's the XML way
select split.csv.value('.', 'varchar(100)') as value
from (
     select cast('<x>' + replace(@roles, ',', '</x><x>') + '</x>' as xml) as data
) as csv
cross apply data.nodes('/x') as split(csv)

如果您使用的是 SQL 2016+，则使用 string_split 更好，但这是 SQL 2016 之前的常见方法。

Answer 6

使用 BULK INSERT，您可以将 csv 文件导入到 sql 表中 -

http://blog.sqlauthority.com/2008/02/06/sql-server-import -csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Answer 7

即使已接受的答案也运行良好。但即使对于数千条记录，我也能更快地获得此功能。创建以下函数并使用。

        IF EXISTS (
            SELECT 1
            FROM Information_schema.Routines
            WHERE Specific_schema = 'dbo'
                AND specific_name = 'FN_CSVToStringListTable'
                AND Routine_Type = 'FUNCTION'
            )
    BEGIN
        DROP FUNCTION [dbo].[FN_CSVToStringListTable]
    END
    GO
    
    CREATE FUNCTION [dbo].[FN_CSVToStringListTable] (@InStr VARCHAR(MAX))
    RETURNS @TempTab TABLE (Id NVARCHAR(max) NOT NULL)
    AS
    BEGIN
            ;-- Ensure input ends with comma
    
        SET @InStr = REPLACE(@InStr + ',', ',,', ',')
    
        DECLARE @SP INT
        DECLARE @VALUE VARCHAR(1000)
    
        WHILE PATINDEX('%,%', @INSTR) <> 0
        BEGIN
            SELECT @SP = PATINDEX('%,%', @INSTR)
    
            SELECT @VALUE = LEFT(@INSTR, @SP - 1)
    
            SELECT @INSTR = STUFF(@INSTR, 1, @SP, '')
    
            INSERT INTO @TempTab (Id)
            VALUES (@VALUE)
        END
    
        RETURN
    END
    GO

---Test like this.

    declare @v as NVARCHAR(max) = N'asdf,,as34df,234df,fs,,34v,5fghwer,56gfg,';
    SELECT Id FROM dbo.FN_CSVToStringListTable(@v)

Answer 8

我是关于您使用已接受的答案中提到的解决方案，但进行更多研究使我使用表值类型：

这些效率更高，并且您不需要 TVF（表值函数）只是为了从 csv 创建一个表。您可以直接在脚本中使用它，或者将其作为表值参数传递给存储过程。类型可以创建为：

CREATE TYPE [UniqueIdentifiers] AS TABLE(
    [Id] [varchar](20) NOT NULL
)