Converting varchar to int SQl server 2005

I have a simple sql table which have two columns Name and Ids both are varchar(10); Now i have value in table like ('Test' ,'1,2,3'). We have another table to get the full details of the employee , but the Ids column in tblEmployee is int. following SQL will give error,

Select * from tblEmployee where Ids in (Select Ids from tblNameId wh开发者_高级运维ere name='Test');

Error is unable to convert 1,2,3 to int. Is their a way to convert all value to int or any other workaround.

Thanks.

Use the following function to split the text

CREATE FUNCTION dbo.Split(@String varchar(8000))     
returns @temptable TABLE (items int)     
as     
begin     
    declare @idx int     
    declare @slice varchar(8000)     

    select @idx = 1     
        if len(@String)<1 or @String is null  return     

    while @idx!= 0     
    begin     
        set @idx = charindex(',',@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String     

        if(len(@slice)>0)
            insert into @temptable(Items) values(CONVERT(INT, @slice))     

        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end 
return     
end

Then

Select * from tblEmployee where Ids in dbo.Split(Select Ids from tblNameId where name='Test');

(adapted from this site: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx)

Looks like you actually have a comma-separated list of integers. You could query like:

select  * 
from    tblEmployee e
join    tblNameId ni
on      ',' + ni.Ids + ',' like '%,' + cast(e.Ids as varchar(12)) + ',%'

A cleaner design would normalize the tblNameId table, with one row for each employee id.