Splitting and aggregating a field with commas

Question

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

paramind SSCrazy Points: 2160 More actions · Answer 1

You're all wrong!

>I have a table with a field that contains comma-delimited data. Here is a sample of two rows and the values for each field<

cat, dog, mouse

mouse, dog, fox, hen

The onlything you really need, is:

Update myTable

Set myString = 'zoo'

Where

myString Like %

This will do for any upcoming values.

_/_/_/ paramind _/_/_/

RyanRandall SSChampion Points: 13623 More actions · Answer 2

This does the trick:

--This code is safe to run

DECLARE @Animals TABLE (ListOfAnimals VARCHAR(100))

INSERT INTO @Animals

SELECT 'cat, dog, mouse' UNION ALL

SELECT ' mouse,dog, fox , hen ' UNION ALL

SELECT 'hen' UNION ALL

SELECT ' hen ' UNION ALL

SELECT 'fox,hen' UNION ALL

SELECT 'mongoose, goldfish,cat' UNION ALL

SELECT 'dog , cat' UNION ALL

SELECT 'pussy cat, big dog'

DECLARE @Numbers_0_to_100 TABLE (i TINYINT identity(0,1), j BIT)

INSERT INTO @Numbers_0_to_100 SELECT TOP 101 NULL FROM master.dbo.syscolumns

SELECT Animal, count(*) AS [Count]

FROM (

SELECT ltrim(rtrim(substring(','+ListOfAnimals, i+1, charindex(',',substring(ListOfAnimals+',', i, 100))-1))) AS Animal

FROM @Animals INNER JOIN @Numbers_0_to_100 ON substring(','+ListOfAnimals, i, 1) = ','

) a

GROUP BY Animal

I agree with all the posts about the design, though (of course). Thanks to Vasc for the basic idea.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

paramind SSCrazy Points: 2160 More actions · Answer 3

You still forgot to integrate a CLR-based-webservice for the retrieval of seldom animal names like the commagoose known for it's beautiful feathers.

_/_/_/ paramind _/_/_/

RyanRandall SSChampion Points: 13623 More actions · Answer 4

Just working on that now...

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.