Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: joining tables using REPLACE

    Here's a weird problem I've had to face recently. We have 2 tables, one a list of diagnosis codes. The other is a lookup table where each diagnosis code can be looked up and matched with a code_name. Problem is, in the diagnosis code table, the codes are listed in one format (a char string like "41001"). In the lookup table, the codes are listed in another format (a char string with a period in the middle, like "410.01").

    I figured it would be a simple procedure to join them, something like this:

    select lu_diag_code.code_name, diag_code.code_id from diag_codes, lu_diag_codes where diag_codes.code_id = replace(lu_diag_codes.code_id,'.','')

    But of course, that'd be too easy and doesn't work. The code_name column returns all nulls.

    Anyone have a solution? Thanks!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If it's not possible to set the 2 columns equal ... ie, strip out the "."
    in 1 table or add a "." in the other table to get the values consistant...
    Then try

    select ... from diagnosis a, code b
    where substr(b.code_id,1,INSTR(b.code_id,'.')-1)||
    substr(b.code_id,INSTR(b.code_id,'.')+1),xxx) = a.code_id

    xxx is the max length of column .... Concatenate using substr...
    If it is a varchar2 column and you don't always know the length of
    the column then use instr ... else if you alway know the 1st 3 chars
    are valid and the "." is always the 4th char, then just substr...

    The use of substr and instr will NOT USE an index on the code table ...
    You might look at making that table an IOT table if it is not too wide
    (ie, not may columns ...)...

    HTH
    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    Works perfectly, thanks Gregg.

Posting Permissions

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