用于拆分字符串和连接子字符串的 SQL 函数 [英] SQL Function to split string and concat substrings

问题描述

我正在尝试创建一个函数，该函数将从表中接收一列，对于单元格中的每个字符串，它将拆分为单独的单词并将每个单词连接起来以创建不同的子字符串.我一直在研究如何使用像数组等的 SQL 来管理它，但我没有运气

I am trying to create a function that will take in a column from a table, for each string in a cell it will split in to separate words and concat each word separatley to create different substrings. I have been looking at how to manage it with SQL like arrays et but I have had no luck

这两个表是:

Account(AccountID(PK), Name, Country)
accountSubstring(subID(PK), AccountID, Substring)

理想情况下，我需要一个函数来接收来自帐户的名称"列.对于每一行，它会将字符串拆分为单独的单词，然后通过字符串 separatley 连接每个单词.然后，这些新创建的子字符串将作为子字符串"连同子字符串所属的原始表中的 AccountID 输入到 accountSubstring 表中.

Ideally I need a function that would take in the column 'Name' from Account. For each row it would split the strings in to separate words and then concat each word through the string separatley. These newly created substrings would then be inputted to the accountSubstring table as 'Substring' along with the AccountID the substring belongs to from the original table.

为了更好地解释它，例如，如果Name"中的名称之一是Stack Overflow Ireland Ltd"，则预期结果将是:

To explain it better, for example, If one of the names in 'Name' was 'Stack Overflow Ireland Ltd', the expected outcome would be:

Stack
StackOverflow
StackOverflowIreland
StackOverflowIrelandLtd
Overflow
OverflowIreland
OverflowIrelandLtd
Ireland
IrelandLtd
Ltd

我需要遍历每个新创建的拆分词并连接到最后一个词.上面的每一个都将作为一个新行插入到 accountSubstring 中，但具有相同的 AccountID，因为它来自相同的原始字符串.

I would need to loop through each newly created split word and concat until it reaches the final word. Each of the above would be inserted into accountSubstring as a new row but with the same AccountID as it came from the same original string.

Account 表有数千行，需要对所有行进行处理.它是一个匹配服务，使用子字符串进行比较.

The Account table has thousands of rows and it will need to be done on all. It is a matching service and the substrings are used for comparison.

希望这很清楚我要做什么，非常感谢.

Hopefully this is clear as to what I am trying to do, Thanks very much in advance.

推荐答案

首先创建数字表.

SELECT TOP (4000)
    n = ISNULL(CONVERT(integer, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 0)
INTO dbo.tblNumber1
FROM sys.columns AS c
CROSS JOIN sys.columns AS c2


CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.tblNumber1 (n)
WITH (MAXDOP = 1, SORT_IN_TEMPDB = ON);

然后创建拆分字符串函数

Then Create Split String function

CREATE FUNCTION [dbo].[DelimitedSplitString] (
    @pString VARCHAR(8000)
    ,@pDelimiter CHAR(1)
    )
RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN
WITH cteStart(N1) AS (
        SELECT 1

        UNION ALL

        SELECT t.number + 1
        FROM dbo.tblNumber1 t
        WHERE number < datalength(@pString)
            AND SUBSTRING(@pString, t.number, 1) = @pDelimiter
        )
    ,cteLen(N1, L1) AS (
        SELECT s.N1
            ,ISNULL(NULLIF(CHARINDEX(@pDelimiter, @pString, s.N1), 0) - s.N1, 8000)
        FROM cteStart s
        )

SELECT ItemNumber = ROW_NUMBER() OVER (
        ORDER BY l.N1
        )
    ,Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l;

最终剧本，

    DECLARE  @Input table( id int identity(1,1),pString VARCHAR(2000)) 
insert into @Input values
('Stack Overflow Ireland Ltd')
,('Replace a sequential set of numbers with special character')

declare @pDelimiter CHAR(1)=' '

declare @t table (id int,itemNum int,Item varchar(50))
INSERT INTO @t
SELECT i.id
    ,ca.ItemNumber
    ,ca.Item
FROM @Input I
CROSS APPLY (
    SELECT ItemNumber
        ,Item
    FROM [dbo].[DelimitedSplitString](pString, @pDelimiter)
    ) ca;

WITH CTE
AS (
    SELECT *
    FROM @t t
        ,dbo.tblNumber1 n
    WHERE n.number <= t.itemNum
    )
SELECT id
    ,(
        SELECT '' + item
        FROM cte c1
        WHERE c.id = c1.id
            AND c.number = c1.number
            AND c1.itemNum <= c.itemNum
        FOR XML path('')
        )
FROM CTE c
ORDER BY id
    ,number
    ,itemNum

这篇关于用于拆分字符串和连接子字符串的 SQL 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！