Search code examples
sqlsqldatatypes

Returning integers from a text field

I need to return certain ID's from a text field in a SQL query. I am having difficulties joining to my temp table as the column I want to return data from that is holding the integers is actually a text field

I am running:

SELECT a.userID,a.name,a.list
FROM table1 a
JOIN table2 tmp
ON a.list = tmp.skill

This returns:

Msg 206, Level 16, State 2, Line 22 Operand type clash: text is incompatible with int

So, tmp.skill is the column that holds the duplicate IDs that I want to dig out in a.list. My table2 has a list of 1300 IDs

Just to add to this, a.list from table1 contains more than one ID. For example:

1234, 12345

So if 12345 appears in tmp.skill in table2 then I want the list of users to return

Solution

  • As you compare text field with int that thats why thrown this error you need to convert int to text then you can use any comparison. so 1st convert skill this way CONVERT(varchar, tmp.skill) then you can use like operator

    SELECT a.userID,a.name,a.list
FROM table1 a
JOIN table2 tmp
ON a.list like '%'+CONVERT(varchar(10), tmp.skill)+'%'