SQL Server:拆分操作 [英] SQL Server: Split operation

问题描述

如何在 SQL Server 中拆分字符串.

How to split a string in SQL Server.

示例:

输入字符串:stack over flow

结果:

stack
over
flow

推荐答案

如果不能使用表值参数，请参见:"Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters" by Erland Sommarskog ，然后在 SQL Server 中有很多方法可以拆分字符串.本文涵盖了几乎所有方法的优点和缺点:

if you can't use table value parameters, see: "Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters" by Erland Sommarskog , then there are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

"SQL Server 2005 及更高版本中的数组和列表，当表值参数时不要剪掉它"作者:Erland Sommarskog

您需要创建一个拆分函数.拆分函数的使用方法如下:

You need to create a split function. This is how a split function can be used:

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

我更喜欢使用数字表方法在 TSQL 中拆分字符串 但在 SQL Server 中有多种拆分字符串的方法，请参阅上一个链接，其中解释了每种方法的优点和缺点.

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

要使 Numbers Table 方法起作用，您需要进行一次时间表设置，这将创建一个表 Numbers，其中包含从 1 到 10,000 的行:

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置 Numbers 表后，创建此拆分函数:

Once the Numbers table is set up, create this split function:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn  char(1)      --REQUIRED, the character to split the @List string on
    ,@List     varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN 
(

    ----------------
    --SINGLE QUERY-- --this will not return empty rows
    ----------------
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

);
GO 

您现在可以轻松地将 CSV 字符串拆分为一个表格并在其上加入:

You can now easily split a CSV string into a table and join on it:

select * from dbo.FN_ListToTable(' ','stack over flow')

输出:

ListValue
-------------------
stack
over
flow

(3 row(s) affected)

这篇关于SQL Server:拆分操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！