Post reply

Datatype Conversion Question

  • June 9, 2009 at 6:26 am

    #384739

    OK you SQL gurus out there, I have an issue that I haven't been able to come up with an acceptable solution to. I receive thousands of Patient data records from many medical practices to place into a central database. Once I receive the data, I extract specific data fields and convert the data into an HL7 message via a few stored procedures, which is then sent and added to this central database or repository.

    My issue is this, one of the clients has an EMR (Electronic Medical Record) system that stores Patient, Physician, and practice names as 36 character GUIDs. I have no problem receiving the data and inserting it into the HL7 message, but the folks dealing with the repository cannot handle 36 or 32 (removing hyphens) characters in the Patient ID, Physician ID, or Practice ID field.

    I've tried taking this 32 character hexadecimal field and converting it to decimal, or converting to binary and then to decimal, but it's not working very well. I'm still coming up with a value that is too long. I've asked if we could expand the length of the field, but I'm getting tons of pushback from them. Another idea I had was to create a cross indexed table with the GUID matched up to a Identity field and using that solution, but I'm afraid that this idea will become a maintenance nightmare.

    Does anyone have any suggestions on how I can overcome this issue? The folks managing the repository can only handle at this time 16 character ID's. Here's an example of one of my ID's...

    "DEFE4787AC2D4351BDA6FFCA07CC0D00", with the hyphens removed.

    Thanks in advance for any help,

    Rick Mills, MT(ASCP)
    Analyst / Programmer
    Indiana Health Information Exchange
    rmills@ihie.com

  • June 9, 2009 at 7:04 am

    #1006652

    If you prefix the guid with 0x, you can convert it to a char, i.e.:-

    select convert(char(16),0xDEFE4787AC2D4351BDA6FFCA07CC0D00)

  • June 9, 2009 at 7:22 am

    #1006670

    Ian...

    When I try

    select convert(char(16),0xDEFE4787AC2D4351BDA6FFCA07CC0D00)

    I get the following:

    "ÞþG‡¬-CQ½¦ÿÊÌ"

    and I'm not sure what that represents!

    One other note, I'm pulling these Hex values from a field within a SQL table, so I have the current datatype to deal with when converting. I eventually end up with an error code of something to the effect that "unable to convert to char datatype" or INT datatype, etc.

    Thanks,

    Rick Mills, MT(ASCP)
    Analyst / Programmer
    Indiana Health Information Exchange
    rmills@ihie.com

  • June 9, 2009 at 7:43 am

    #1006682

    That is exactly what I would expect, as that is the character representation of the hex string i.e. Hex DE is "Þ", Hex FE is "þ" etc.

    select convert(char(16),0x50515253545556575859606162636465)

    would yield PQRSTUVWXY`abcde, as Hex 50 is "P", Hex 51 is Q etc.

    A lot of the guid is going to be in the "unprintable" range, but at least it has reduced the size to 16 characters.

  • June 9, 2009 at 8:57 am

    #1006755

    Just a little sample which shows that Ians convert works:

    DECLARE @C CHAR(16)

    SELECT @C = CONVERT(CHAR(16),0xDEFE4787AC2D4351BDA6FFCA07CC0D00)

    SELECT @C, CONVERT(BINARY(16), @C)

    Greets

    Flo

  • June 9, 2009 at 12:55 pm

    #1007005

    Rick Mills (6/9/2009)

    ...The folks managing the repository can only handle at this time 16 character ID's. ..

    What is the exact datatype definition of this 16 character field? (it's very important).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 9, 2009 at 3:59 pm

    #1007106

    Maybe use a CHECKSUM / BINARY_CHECKSUM on the GUID? Not exact though but could be used with something else maybe??

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply