If the -'s are there in every string, you can use CHARINDEX to extract the middle piece.

DECLARE @String varchar(60)

SET @String = 'CSCAR009/0001/001-PASPBD-Pedestrian automatic sliding door'

SELECT LEFT(RIGHT(@String,LEN(@String)-18),(CHARINDEX('-',RIGHT(@String,LEN(@String)-18) ) )-1)

The best thing...

If I'm reading this correctly, you'd want to do something like this:

UPDATE table_A

SET C_Flag = 'W'

FROM table_A A

INNER JOIN table_B B ON A.X_CODE = B.Y_CODE

LEFT JOIN...