ansaurus

Question

HOWTO get distinct characters of string column in mssql

Answer 1

+1 A:

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 2010-10-13 13:06:57

`Msg 8152, Level 16, State 14, Line 3String or binary data would be truncated.`

Andreas Niedermair 2010-10-13 13:09:17

Talk about a sledgehammer to crack a nut. That's AWFUL.

smirkingman 2010-10-13 13:11:50

@smirkingman - Waiting for your better solution :-). Andreas - That error doesn't come from my code. How are you using it?

Martin Smith 2010-10-13 13:15:11

i've only replaced the union-part with `select column from table` (typeof varchar(400))

Andreas Niedermair 2010-10-13 13:17:57

thanks ... jup! my mistake!

Andreas Niedermair 2010-10-13 13:22:52

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 Kashif Nadeem 2010-10-13 18:09:16

@Muhammad - Is it clearer if you comment out `SELECT @result = ... ORDER BY MIN(RN)` and replace it with ` SELECT * FROM Letters`?

Martin Smith 2010-10-13 18:54:33

@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.

Muhammad Kashif Nadeem 2010-10-14 17:18:43

@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 2010-10-14 23:00:21

Answer 2

+1 A:

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 2010-10-13 13:11:47

wow ... gently ... but one problem: `Msg 8152, Level 16, State 14, Line 5 String or binary data would be truncated.`. the only thing i've changed is the insert into now selecting `select column from table` (typeof varchar(400))

Andreas Niedermair 2010-10-13 13:18:47

Did you adjust the `name` column from `varchar(100)` to `varchar(400)` in the declaration of the `@Names` table?

Joe Stefanelli 2010-10-13 13:26:25

jup ... THANKS!! my mistake!

Andreas Niedermair 2010-10-13 13:26:57

It took 25 seconds to complete. Isn't it awful lot of time?

Muhammad Kashif Nadeem 2010-10-13 13:47:18

@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 2010-10-13 13:51:55

@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 2010-10-13 18:31:13

@Martin: Good stuff. Thanks for sharing.

Joe Stefanelli 2010-10-13 19:20:35

Answer 3

+1 A:

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

Muhammad Kashif Nadeem 2010-10-14 17:18:48

ansaurus

tags:

views:

answers:

HOWTO get distinct characters of string column in mssql

related questions