Can I split a field based on a character?

Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.

Don't let blindman see this post; he'll pull out the complete works of E.F. Codd!!! :D

Seriously, do a google on fn_Split(). I think you will find it will suit your purposes.

Regards,

hmscott|||check that site,I think that is the best and simple way to do.
http://www.sqlteam.com/item.asp?ItemID=2652|||The requirements are very close to the ones of generating a remittance advice when running Claims Reimbursement...It's been awhile, but I had to deal with the same scenario. First, I stored Dx codes separately from Claim Details. I did have a reference from ClaimDxCodes back to Claim Details table to retain dependency of CPT codes on diagnosis codes (Dx). When printing the Remittance Advice I transformed the data to present it in the format that you're trying to accomplish, using Crystal (the original app was written in VB4 with SQL 6.5). Later, when I recoded the reporting piece for ActiveReports control, I created a sub-report to display the Dx codes as a comma-separated list. But the concept remained the same, - ClaimHeader-->>ClaimDetail-->>ClaimDxCodes. Do not store Dx codes in the same table as your CPT codes, or your claim header info! Doing so violates the fundamentals of relational database principles, and B. Lindman WILL pull out the complete works of Codd at you...Let the massacre begin!..|||That's got me out of a tight spot, thanks very much.
Cheers, Simon.|||I'm really a nice guy. Really.