如何在MSSQL中获得字符串列的不同字符?

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

这是一个相当常见的SQL难题。您需要一个数字表,我将在这里使用CTE生成它(假设SQL Server 2005或更高版本)。

declare @Names table (
    name varchar(100)
)

insert into @Names
    (name)
    select 'Josef Knoller' union all
    select 'Josef Somos' union all
    select 'KFZ Wiesauer'

;With Numbers As (
    Select Row_Number() Over ( Order By c1.object_id ) As Value
    From sys.columns As c1
        Cross Join sys.columns As c2
)
Select Distinct '' + case when Substring(nm.name, N.Value, 1)<>' ' then upper(Substring(nm.name, N.Value, 1)) else '' end
    From Numbers N
        Cross Join @Names nm
    Where N.Value <= Len(nm.name)
    For Xml Path('')

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 nums(i) as
(
    select i=1
    union all
    select i=i+1 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 nums ON nums.i <= LEN(name))

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

SELECT @result