Distinct values when some are comma seperated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Distinct values when some are comma seperated

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2007-12-11 : 14:26:56

Hi, didn't see this answered anywhere regarding splitting comma delimited values, if it has then a link would be great.

I was given a table with a Categories column where some of the values are comma separated. When I do this:

select Distinct RTrim(categories) from artsandcrafts
where categories <> ''

I get:

Accessories
Accessories, Home decor
Clothing
Clothing, Home decor
Furniture, Home decor
Furniture, Sculpture
Home decor
Home decor, Jewellery
Home decor, Sculpture
Jewellery
Sculpture

I need to reduce this record set to only return:

Accessories
Clothing
Furniture
Home decor
Jewellery
Sculpture

The real distinct values as it were. Can anyone help me on this one? A function would be great as I have several columns like this.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 14:33:01

[code]DECLARE @Sample TABLE (Info VARCHAR(200))

INSERT @Sample
SELECT 'Accessories' UNION ALL
SELECT 'Accessories, Home decor' UNION ALL
SELECT 'Clothing' UNION ALL
SELECT 'Clothing, Home decor' UNION ALL
SELECT 'Furniture, Home decor' UNION ALL
SELECT 'Furniture, Sculpture' UNION ALL
SELECT 'Home decor' UNION ALL
SELECT 'Home decor, Jewellery' UNION ALL
SELECT 'Home decor, Sculpture' UNION ALL
SELECT 'Jewellery' UNION ALL
SELECT 'Sculpture'

SELECT Info
FROM (
SELECT PARSENAME(REPLACE(Info, ', ', '.'), 1) AS Info
FROM @Sample

UNION

SELECT PARSENAME(REPLACE(Info, ', ', '.'), 2)
FROM @Sample

UNION

SELECT PARSENAME(REPLACE(Info, ', ', '.'), 3)
FROM @Sample

UNION

SELECT PARSENAME(REPLACE(Info, ', ', '.'), 4)
FROM @Sample
) AS d
WHERE Info IS NOT NULL
ORDER BY Info[/code]

E 12°55'05.25"
N 56°04'39.16"

littlewing
Starting Member

33 Posts

Posted - 2007-12-11 : 14:58:42

Thanks Peso, that works like a charm. Excuse my ignorance but can you briefly explain the

SELECT PARSENAME(REPLACE(Info, ', ', '.'), 1) AS Info
FROM @Sample

UNION

SELECT PARSENAME(REPLACE(Info, ', ', '.'), 2)
FROM @Sample

UNION ...

part of your solution? Not quites sure what's going on here.
Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 15:17:03

You only need

SELECT DISTINCT	Categories
FROM		(
			SELECT	PARSENAME(REPLACE(Categories, ', ', '.'), 1) AS Categories
			FROM	ArtsAndCrafts 

			UNION ALL

			SELECT	PARSENAME(REPLACE(Categories, ', ', '.'), 2)
			FROM	ArtsAndCrafts

			UNION ALL

			SELECT	PARSENAME(REPLACE(Categories, ', ', '.'), 3)
			FROM	ArtsAndCrafts

			UNION ALL

			SELECT	PARSENAME(REPLACE(Categories, ', ', '.'), 4)
			FROM	ArtsAndCrafts
		) AS d
WHERE		Categories IS NOT NULL
ORDER BY	Categories

All other is only to mimic your environment.

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-11 : 16:09:04

PARSENAME is a SQL function to get an Object part. For example you can use 4-part naming to identify an object: MyServer.MyDataBase.dbo.MyTable. Using PARSENAME you get a specific part of the object:
1 = Object name
2 = Schema name
3 = Database name
4 = Server name

The replace is just making your strings conform to the use the dot instead of the comma so that PARSENAME will work on it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 02:06:59

parsename comes handy when you want to split data as long as you data contains four or lesser number of parts

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources