How to match on string length to get the data?

Question

I have emp1 and emp2 tables. I am trying to update few values from emp2 to emp1 by mathcing emp_id. But the emp1 has correct emp_id where as emp2 has additional characters at the end. How do I match the emp_ids based on length of the characters( (Ex: if 3 characters in emp1 then match on first 3 chars in emp2, 4 characters in emp1 then match on first 4 characters etc.)

**TABLE: EMP1                                  TABLE2: EMP2**
    
EMP_ID  DEPT                                  EMP_ID      DEPT
                                            
AAA      Accounts                            AAA12        Accounts
BBBBB    HR                                  BBBBBrcp      HR 

-Thanks in advance.

Answer 1

You can join on like. It is unclear what you want to update in emp2, but the logic is:

update emp2 e2
set col1 = e1.col1, col2 = e1.col2  -- columns you want to update go here
from emp1 e1
where e2.emp_id like e1.emp_id || '%'

Beware that each employee in emp2 should match on only one row in emp1, otherwise unexpected things may happen.