Sql join int varchar

Question

I searched for questions but could not find an answer. I would like to make an internal join with table 1 and table 2, but one field is INT and the other is VARCHAR (with the structure "A-INT"). Sort of:

Table1 Table2 ID NAME WHATEVER ID USER 1 A-001 --- 1 001 2 A-002 --- 2 002 3 A-003 --- 3 003 3 A-004 --- 4 004 ... 

How can I build a query? Sort of:

 ... Table1 INNER JOIN Table2 ON Table1.NAME = Table2.[A-USER] ... 

Answer 1

You will need to convert the int value to varchar and concatenate it with 'A-' or get the numeric part of the varchar value and convert to int .

This is done a little differently, depending on which database you are using, for SQL Server it will look like this:

 select t1.ID, t1.WHATEVER, t2.USER from Table1 t1 inner join Table2 t2 on t2.ID = cast(substring(t1.NAME, 3, 3) as int) 

Answer 2

Try concatenating

 SELECT * FROM Table1 a, Table2 b WHERE a.Name = 'A-' + b.User 

but note that this reduces performance.

Answer 3

You want to make an internal join in Table1.NAME = Table 2. [A-USER].

If the column type is Table1.NAME, Table2. [A-USER] - these are different means, it is impossible to use an internal connection.