USE Northwind
GO

CREATE TABLE myTable99 (myKey char(1) NOT NULL, col1 varchar(8000))
GO

INSERT INTO myTable99 (myKey, col1) 
SELECT 'A', 'Window Washer, Application DBA, SQL Scrub' UNION ALL
SELECT 'B', 'SQL MVP, SQL MVY, SQL Pimp'
GO 


CREATE PROC mySPROC AS

DECLARE @myKey char(1), @col1 varchar(8000), @x int, @NewString varchar(255)

DECLARE @tmpTable Table (myNewKey char(1), skill varchar(256))

SET NOCOUNT ON

SELECT @x = -1

DECLARE csrParser CURSOR READ_ONLY FOR 
	SELECT myKey, Col1 FROM myTable99

OPEN csrParser

FETCH NEXT FROM csrParser
	INTO	@myKey, @col1

SELECT @x = charindex(',',@col1,1)

WHILE @@FETCH_STATUS = 0
	BEGIN 
		WHILE @x <> 0
			BEGIN
				SELECT @NewString = SUBSTRING(@col1,1,@x-1)
				INSERT INTO @tmpTable(myNewKey, skill) SELECT @myKey,LTRIM(RTRIM(@NewString))
				SELECT @Col1 = SUBSTRING(@Col1,@x+1,LEN(@col1)-LEN(SUBSTRING(@col1,1,@x+1)))
				SELECT @x = charindex(',',@col1,1)
			END	

		INSERT INTO @tmpTable(myNewKey, skill) SELECT @myKey,LTRIM(RTRIM(@Col1))
		FETCH NEXT FROM csrParser
		INTO	@myKey, @col1
		SELECT @x = charindex(',',@col1,1)
	END

SELECT myNewKey, skill FROM @tmpTable

CLOSE csrParser
DEALLOCATE csrParser

SET NOCOUNT OFF
Go

EXEC mySPROC
GO

DROP PROC mySPROC
GO

DROP TABLE myTable99
GO

CREATE TABLE #TALLY (UID INT)

DECLARE @COUNTER INT
SET @COUNTER = 1
WHILE @COUNTER <=1000
BEGIN
INSERT INTO #TALLY(UID)
SELECT @COUNTER
SET @COUNTER = @COUNTER +1
END

DECLARE @SKILLS VARCHAR(2000)
SELECT @SKILLS = 'FOOT BALL,CRICKET,TENNIS,GOLF,SNOOKER,BASKET BALL'

SELECT	SUBSTRING(',' + @SKILLS + ',', UID, CHARINDEX(',' , ',' + @SKILLS + ',' , UID) - UID)
FROM	#TALLY
WHERE UID <= LEN(', ' + @SKILLS + ', ')
AND	SUBSTRING(',' + @SKILLS + ', ', UID - 1, 1) = ',' 
AND	CHARINDEX(', ' , ',' + @SKILLS + ', ' , UID) - UID > 0 

quote:

---

I tried running it on the SQL Server 2000 but it wouldn't allow me to create a table on the Northwind DB

---

quote:

---

Oh yeah, that's right..the tally table...

I like that soooooooooo much better than mine...it's definetly slicker..

Which one do you think performs better though...

I bet your's does and NO Cursor!

WOOHOO!

---

SELECT	LTRIM(RTRIM(SUBSTRING(',' + col1 + ',', UID, CHARINDEX(',' , ',' + col1 + ',' , UID) - UID)))
FROM	#TALLY, myTable99
WHERE UID <= LEN(', ' + col1 + ', ')
AND	SUBSTRING(',' + col1 + ', ', UID - 1, 1) = ',' 
AND	CHARINDEX(', ' , ',' + col1 + ', ' , UID) - UID > 0 

quote:

---

What is your problem with me Brett?

---

quote:

---

So yeah right, the tally table (he said with much unabashed joy).
It's something I need to employye more, esp when it looks like a cursor (BOOO) is the only answer.

---

You're not the only one Brett, but you know what, the next time you come across this question you will do it with a Tally table...

There is so much to learn it is depressing sometimes. Are we ever allowed to feel proud of what we know or is there always a smart ass around the corner with a better solution?!

... there is always a smart ass around every corner

Shadow to Light

quote:

---

There is so much to learn it is depressing sometimes. Are we ever allowed to feel proud of what we know

---