問題描述

我已經研究了一段時間，但找不到從 SQL Server 中的逗號分隔字符串和管道分隔字符串中刪除重復字符串的方法.

I have been looking into this for a while now and I cannot find a way to remove duplicate strings from a comma-separated as well as pipeline seperated string in SQL Server.

給定字符串

test1,test2,test1|test2,test3|test4,test4|test4

有誰知道你會如何返回 test1,test2,test3,test4?

does anyone know how would you return test1,test2,test3,test4?

推薦答案

方法

以下方法可用于對分隔的值列表進行重復數據刪除.

The following approach can be used to de-duplicate a delimited list of values.

1. 使用REPLACE() 函數將不同的分隔符轉換為相同的分隔符.
2. 使用 REPLACE() 函數注入 XML 結束和開始標記以創建 XML 片段
3. 使用CAST(expr AS XML)函數將上述片段轉換為XML數據類型
4. 使用 OUTER APPLY 應用表值函數 nodes() 將 XML 片段拆分為其組成的 XML 標記.這將在單獨的行中返回每個 XML 標記.
5. 使用 value() 函數僅從 XML 標記中提取值，并使用指定的數據類型返回值.
6. 在上述值后附加一個逗號.
7. 請注意，這些值在不同的行中返回.DISTINCT 關鍵字的使用現在可以刪除重復的行(即值).
8. 使用 FOR XML PATH('') 子句將多行中的值連接成一行.

1. Use the REPLACE() function to convert different delimiters into the same delimiter.
2. Use the REPLACE() function to inject XML closing and opening tags to create an XML fragment
3. Use the CAST(expr AS XML) function to convert the above fragment into the XML data type
4. Use OUTER APPLY to apply the table-valued function nodes() to split the XML fragment into its constituent XML tags. This returns each XML tag on a separate row.
5. Extract just the value from the XML tag using the value() function, and returns the value using the specified data type.
6. Append a comma after the above-mentioned value.
7. Note that these values are returned on separate rows. The usage of the DISTINCT keyword now removes duplicate rows (i.e. values).
8. Use the FOR XML PATH('') clause to concatenate the values across multiple rows into a single row.

查詢

將上述方法放在查詢表單中:

Putting the above approach in query form:

SELECT DISTINCT PivotedTable.PivotedColumn.value('.','nvarchar(max)') + ',' 
FROM ( 
        -- This query returns the following in theDataXml column: 
        -- <tag>test1</tag><tag>test2</tag><tag>test1</tag><tag>test2</tag><tag>test3</tag><tag>test4</tag><tag>test4</tag><tag>test4</tag>
        -- i.e. it has turned the original delimited data into an XML fragment 
        SELECT 
          DataTable.DataColumn AS DataRaw 
        , CAST( 
            '<tag>' 
            -- First replace commas with pipes to have only a single delimiter 
            -- Then replace the pipe delimiters with a closing and opening tag 
            + replace(replace(DataTable.DataColumn, ',','|'), '|','</tag><tag>') 
            -- Add a final set of closing tags 
            + '</tag>' 
            AS XML) AS DataXml 
        FROM ( SELECT 'test1,test2,test1|test2,test3|test4,test4|test4' AS DataColumn) AS DataTable 
    ) AS x 
OUTER APPLY DataXml.nodes('tag') AS PivotedTable(PivotedColumn) 
-- Running the query without the following line will return the data in separate rows 
-- Running the query with the following line returns the rows concatenated, i.e. it returns: 
-- test1,test2,test3,test4, 
FOR XML PATH('') 

輸入&結果

給定輸入:

test1,test2,test1|test2,test3|test4,test4|test4

test1,test2,test1|test2,test3|test4,test4|test4

上面的查詢會返回結果:

The above query will return the result:

測試1，測試2，測試3，測試4，

test1,test2,test3,test4,

注意末尾的尾隨逗號.我將把它作為練習留給你來刪除它.

Notice the trailing comma at the end. I'll leave it as an exercise to you to remove that.

重復次數

OP 在評論中請求我如何獲得重復的計數?在單獨的列中".

OP requested in a comment "how do i get t5he count of duplicates as well? in a seperate column".

最簡單的方法是使用上述查詢，但刪除最后一行 FOR XML PATH('').然后，計算上述查詢中 SELECT 表達式返回的所有值和不同值(即 PivotedTable.PivotedColumn.value('.','nvarchar(max)')).所有值的計數與不同值的計數之間的差值是重復值的計數.

The simplest way would be to use the above query but remove the last line FOR XML PATH(''). Then, counting all values and distinct values returned by the SELECT expression in the above query (i.e. PivotedTable.PivotedColumn.value('.','nvarchar(max)')). The difference between the count of all values and the count of distinct values is the count of duplicate values.

SELECT 
    COUNT(PivotedTable.PivotedColumn.value('.','nvarchar(max)'))            AS CountOfAllValues 
  , COUNT(DISTINCT PivotedTable.PivotedColumn.value('.','nvarchar(max)'))   AS CountOfUniqueValues 
    -- The difference of the previous two counts is the number of duplicate values 
  , COUNT(PivotedTable.PivotedColumn.value('.','nvarchar(max)')) 
    - COUNT(DISTINCT PivotedTable.PivotedColumn.value('.','nvarchar(max)')) AS CountOfDuplicateValues 
FROM ( 
        -- This query returns the following in theDataXml column: 
        -- <tag>test1</tag><tag>test2</tag><tag>test1</tag><tag>test2</tag><tag>test3</tag><tag>test4</tag><tag>test4</tag><tag>test4</tag>
        -- i.e. it has turned the original delimited data into an XML fragment 
        SELECT 
          DataTable.DataColumn AS DataRaw 
        , CAST( 
            '<tag>' 
            -- First replace commas with pipes to have only a single delimiter 
            -- Then replace the pipe delimiters with a closing and opening tag 
            + replace(replace(DataTable.DataColumn, ',','|'), '|','</tag><tag>') 
            -- Add a final set of closing tags 
            + '</tag>' 
            AS XML) AS DataXml 
        FROM ( SELECT 'test1,test2,test1|test2,test3|test4,test4|test4' AS DataColumn) AS DataTable 
    ) AS x 
OUTER APPLY DataXml.nodes('tag') AS PivotedTable(PivotedColumn) 

對于上面顯示的相同輸入，此查詢的輸出是:

For the same input shown above, the output of this query is:

CountOfAllValues CountOfUniqueValues CountOfDuplicateValues
---------------- ------------------- ----------------------
8                4                   4

這篇關于從逗號或管道運算符字符串中刪除重復項的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！