CODEWITHSUNDEEP
sqlsql-server
Thom Ash
Thom Ash

Reputation: 221

join on string column containing numbers that have dissimilar format

Looking for some help. I have no idea how to go about this.

I have two tables and I need to get data from ICD10Code column into the other ICD10Code column but the column I need to join on, ICD9Code, has some format differences where numbers are stored as string and trailing zeroes and decimal are involved.

The 1st table has columns ReportingMeasure, ICD9Code, and ICD10Code. The ICD10Code is null. I need to populate ICD10Code from the 2nd table.

The 2nd table has columns ICD9Code, and ICD10Code. Now the problem. Both columns are varchar(25) but table 1 doesn’t have trailing zeros and sometimes an integer has a trailing decimal and sometimes not. Table 2 has trailing zeros and integers have a decimal and trailing zero.

TableST        TablePR
ICD9Code       ICD9Code
296.2          296.20
311. OR 311    311.0

This code works fine when there is a match but not for examples shown.

UPDATE TableST
SET ICD10Code = t2.ICD10Code
FROM TableST t1
INNER JOIN TablePR t2 ON t1.ICD9Code = t2.ICD9Code
GO

How do I go about updating the ICD10Code on rows with irregular ICD9Code?

Upvotes: 2

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Assuming the codes are always numbers, you can try joining on a decimal version:

on cast(t1.ICD9Code as decimal(10, 3)) = cast(t2.ICD9Code as decimal(10, 3))

The "3" is an arbitrary value for the maximum number of digits after the decimal place (it seems reasonable in this case). Conversion to decimals is exact. You would not want to use a floating point representation.

Another solution that might work in your case is to use like:

on t1.ICD9Code = t2.ICD9Code or
   ((t1.ICD9Code like '%.%' and t1.ICD9Code = t2.ICD9Code + '0') or
    (t1.ICD9Code not like '%.%' and t1.ICD9Code = t2.ICD9Code + '.0')
   )

Upvotes: 1

Related Questions