問題描述

我正在嘗試創建一個函數，該函數將從表中接收一列，對于單元格中的每個字符串，它將拆分為單獨的單詞并將每個單詞連接起來以創建不同的子字符串.我一直在研究如何使用像數組等的 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 函數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！