SQL query: Join on similiar fields

Greetings Experts,

I have a situation where I need to join tables on a fields with different values.   In one table, the patient# uses hyphens and the second table does not.  I am joining the tables on patient#.  Any idea how i can do this?  Maybe i can create another table/qry/view that strips out the hyphen?
Here is an example of my problem,

Table 1                              
Study                Patient#      
StudyA             A01-101
StudyA             A02-103
StudyA             A101-103


Table 2
Study                Patient#      
StudyA             A01101
StudyA             A02103
StudyA             A101103

Thanks.



                 
PharmicaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you could consider this:
select ... 
  from table1 t1
  join table2 t2
    on replace(t1.patient#, '-', '') = t2.Patient#

Open in new window

0
 
RPCITCommented:
if it's just joining without the hyphens.. this should work.

SELECT *
FROM Table1
JOIN Table2
     ON REPLACE(Table1.Patient#,'-','') = Table2.Patient#
0
 
PharmicaAuthor Commented:
Thanks for your help!
0
All Courses

From novice to tech pro — start learning today.