4

Given:

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

wanted result:

JOSEFKNMLRZWIAU

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

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

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

MLRZWIAU

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

clearer?

3 Answers3

3

This is a fairly common SQL puzzle. You'll need a Numbers table, which I'll generate here using a CTE (assumes SQL Server 2005 or greater).

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('')
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • It took 25 seconds to complete. Isn't it awful lot of time? – Kashif Oct 13 '10 at 13:47
  • @Muhammad: Because of my technique for generating the Numbers table, it may depend on which DB you're running this in. Try running it in something relatively small like Model, which runs in 1 second for me. I like the technique in [Martin's answer](http://stackoverflow.com/questions/3923830/howto-get-distinct-characters-of-string-column-in-mssql/3923947#3923947) for generating a Numbers table of exactly the right size. – Joe Stefanelli Oct 13 '10 at 13:51
  • 2
    @Joe - Just did some testing on this as I'd been meaning to get around to this for quite a while and then found [this answer](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232) which agrees with the conclusion I came too. Namely that Itzik Ben Gan's cross joined CTE method is the best way to generate a non permanent table of numbers. It is also faster than a permanent table in my test with a cold cache but beaten by the permanent table once the table is cached. – Martin Smith Oct 13 '10 at 18:31
2
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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Talk about a sledgehammer to crack a nut. That's AWFUL. – smirkingman Oct 13 '10 at 13:11
  • 3
    @smirkingman - Waiting for your better solution :-). Andreas - That error doesn't come from my code. How are you using it? – Martin Smith Oct 13 '10 at 13:15
  • Hi Martin, can you please explain or give me a link from where I can read the part after ',' i.e. 'Letters' in CTE. I am not getting the idea that how it is working. – Kashif Oct 13 '10 at 18:09
  • @Muhammad - Is it clearer if you comment out `SELECT @result = ... ORDER BY MIN(RN)` and replace it with ` SELECT * FROM Letters`? – Martin Smith Oct 13 '10 at 18:54
  • @Martin, thanks for your feedback. I have read few good articles on CTE and now I got the whole idea. I have also answer this question using your answer just to ask you that why you have used multiple tables to generate list of nums in FilteredNums table? Please see my answer and comment that what is wrong with that approach to generate nums. – Kashif Oct 14 '10 at 17:18
  • @Muhammad- The cross joined CTE approach is quicker than the recursive CTE approach. See this answer for bench marks http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232 – Martin Smith Oct 14 '10 at 23:00
1
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
Kashif
  • 14,071
  • 18
  • 66
  • 98