如何在mssql中获取字符串列的不同字符？ [英] How do I get distinct characters of string column in mssql?

问题描述

给出：

 | name
-+---------------------------
 | Josef Knoller
 | Josef Somos
 | KFZ Wiesauer

想要的结果：

JOSEFKNMLRZWIAU

（结果的大小无关紧要-只是

(case in the result does not matter - it was just easier holding the UPPER key while writing)

在 T-SQL 中有什么方法可以做到这一点吗？

Is there any way to do this in T-SQL?

抱歉...我混合了列和行...
是1列和n行

sorry ... i've mixed column and row ... it's 1 column and n rows

MLRZWIAU

• M来自 Somos


• L来自 Knoller


• R来自 Knoller


• ...

• M comes from Somos
• L comes from Knoller
• R comes from Knoller
• ...

更清晰？

推荐答案

DECLARE @result VARCHAR(MAX)
SET @result = ''

DECLARE  @t TABLE(name VARCHAR(400))

INSERT INTO @t 
SELECT 'Josef Knoller' UNION ALL SELECT 'Josef Somos' UNION ALL SELECT 'KFZ Wiesauer'

;WITH 
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4),
FilteredNums AS (SELECT i FROM Nums WHERE i<= 400),
Letters AS(
SELECT UPPER(SUBSTRING(name,i,1)) AS L, ROW_NUMBER() OVER (ORDER BY name,i) AS RN
FROM @t
JOIN FilteredNums ON FilteredNums.i <= LEN(name))

SELECT @result = @result + L 
FROM Letters
GROUP BY L
ORDER BY MIN(RN)

SELECT @result

这篇关于如何在mssql中获取字符串列的不同字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！