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.
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...
select ... from diagnosis a, code b
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 ...)...