How to update table by closet match of two table columns and replace wiht one value in one table using sql?

I have two tables with the following values

Table A          Table B
PartNo            PartNo
3AL9723AA        3AL9723AAA01  
3AL7881BA        3AL7881BAA02
1AB5677FC        1AB5677FCD02

Now I want to compare these two PartNo values upto 9 characters and if the 9 characters match then replace the PartNo value in Table B with PartNo in Table A and neglect or remove last three characters,

Please can anyone help me to write the query for 开发者_JAVA百科this problem.

In MySQL:

UPDATE tableA a, tableB b 
SET b.PartNo=a.PartNo 
WHERE LEFT(a.PartNo, 9) = LEFT(b.PartNo, 9);

In MS SQL:

-- Setup

declare @TableA table(PartNo varchar(20))

declare @TableB table(PartNo varchar(20))

insert @TableA 
values
    ('3AL9723AA'),
    ('3AL7881BA'),
    ('1AB5677FC')

insert @TableB
values
    ('3AL9723AAA01'),
    ('3AL7881BAA02'),
    ('1AB5677FCD02')

-- Query

update b
set PartNo = a.PartNo
from @TableA a
    join @TableB b on
        left(a.PartNo, 9) = left(b.PartNo, 9)

-- Result check

select *
from @TableB