Join Tables on Variables using a Begin With or Like Function

[capuanjm]

I have a set of diagnosis codes that were provided with an X variable, where the X represents some numeric value.  Ex. 410.0x.  The x can be a 1 or 2.  I want to join two tables 1) has the 410.0x and the 2) has the 410.01 and 410.02.  How do I link the tables on the variable that allows for a Begins with '410.0' and returns the 410.01 and 410.02

[skg74_mail_umkc_edu]

Do you have common variable for merging two datasets or tables other than Diagnostic codes?

[capuanjm]

No. The second table is a dimension tables to serve as a lookup.

[Reeza · Accepted solution]

When you join you can include functions in the join. So substr the variable if you want only the first 5 digits to match

[PGStats]

You could join on the condition:

translate(code1, "1", "x") = code2 OR translate(code1, "2", "x") = code2