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?

Improve this question
4
  • Hmm, I think I don't understand the logic of the result assuming the input. Commented Oct 13, 2010 at 12:57
  • Is that 3 different rows and you want the distinct characters from all 3 combined? Commented Oct 13, 2010 at 12:57
  • what are you trying to achieve? Commented Oct 13, 2010 at 12:57
  • 1
    how did you come up with that particular type of string? where did MLRZWIAU come from? Commented Oct 13, 2010 at 12:57

3 Answers 3

Reset to default
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('')
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

It took 25 seconds to complete. Isn't it awful lot of time?
@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 for generating a Numbers table of exactly the right size.
@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 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.
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
Improve this answer

6 Comments

Talk about a sledgehammer to crack a nut. That's AWFUL.
@smirkingman - Waiting for your better solution :-). Andreas - That error doesn't come from my code. How are you using it?
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.
@Muhammad - Is it clearer if you comment out SELECT @result = ... ORDER BY MIN(RN) and replace it with ` SELECT * FROM Letters`?
@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.
|
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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.