Possible to parse a column in a Select statement?

[grok]

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?

[John Bell]

Hi

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

John
"grok" <jo**********@verizon.net> wrote in message
news:y3*****************@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?

[Nigel Rivett]

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!