Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8

    Unanswered: 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.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by SSSimon
    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!!!

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

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    check that site,I think that is the best and simple way to do.
    http://www.sqlteam.com/item.asp?ItemID=2652

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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!..
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8

    Thanks all for your quick responses

    That's got me out of a tight spot, thanks very much.
    Cheers, Simon.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm really a nice guy. Really.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •