Possible to parse a column in a Select statement?

I have a column called SEGMENTED_BLOCK sample data:
X,X,X
XX,XX,XX,
TYZC123456,X,X,
TOYZ654321,1234,777777

I need to do something that has the effect of

SELECT
(stuff before first comma) as FIRST_ITEM,
(stuff after first comma, but before second) as NEXT_ITEM,
(stuff after second comma but before third(if any)) as THIRD_ITEM
FROM SEGMENT_XREF
WHERE LOOKUP_ITEM = 12345
ORDER BY FIRST_ITEM

FIRST_ITEM is pretty easy, but it gets uglier fast.
My attempts are horrendously ugly nested checkindex and substring statements.
Is there an easier way?Hi

Charindex is the usual way to segment strings even when it is like:
http://www.users.drew.edu/skass/sql...unction.sql.txt

John
"grok" <joe.hurzeler@.verizon.net> wrote in message
news:y36Wc.6100$O%4.4380@.nwrddc04.gnilink.net...
I have a column called SEGMENTED_BLOCK sample data:
X,X,X
XX,XX,XX,
TYZC123456,X,X,
TOYZ654321,1234,777777

I need to do something that has the effect of

SELECT
(stuff before first comma) as FIRST_ITEM,
(stuff after first comma, but before second) as NEXT_ITEM,
(stuff after second comma but before third(if any)) as THIRD_ITEM
FROM SEGMENT_XREF
WHERE LOOKUP_ITEM = 12345
ORDER BY FIRST_ITEM

FIRST_ITEM is pretty easy, but it gets uglier fast.
My attempts are horrendously ugly nested checkindex and substring
statements.
Is there an easier way?|||see
http://www.nigelrivett.net/f_GetEntryDelimiitted.html

It's a function that returns entries from a csv string.
I use it for gettig fields from data after bulk inserting but it can be
used for a single string too.

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!