Conversion failed when converting the varchar value 'T70001' to data type int?

Question

I am receiving the above error when using the below query

 SELECT  b.*,E.SSNO
  FROM [SRV-RVS].[dbo].[CARD] b
  INNER JOIN [SRV-RVS].dbo.EMP e
  on b.EMPID=E.SSNO 
  WHERE E.SSNO LIKE 't%' 

I am trying to join both tables, here my EMPID is same as SSNO but it got a character in the begining .

Hope you got it

Regards

Answer 1

You could convert EMPID to a varchar and add a 'T' character in the join clause.

SELECT  b.*,E.SNO
FROM [SRV-RVS].[dbo].[CARD] b INNER JOIN [SRV-RVS].dbo.EMP e ON ('T' + REPLACE(STR(CAST(b.EMPID as varchar(9)), 9), SPACE(1), '0')) = E.SSNO 
WHERE E.SNO LIKE 't%' 

---

Additional notes

• I guessed on the length of the varchar but SSN is generally always 9 digits. If you are storing mask/space characters like - in your SSNO column this code will not work.
• There is code in there to left 0 pad the id for SSN numbers that start with 0 as an int to string will not automatically pad 0.
• If there data sets are large this could cause performance problems.
• Really the schema should never have converted SSN to an int (numeric) to begin with. It should have stayed as a varchar field and ideally not set as a primary key on another table either.

Again (continue from last bullet), change the schema or add a computed column. This is currently a poor design.