locked
select * from table where id = 1 and field in(1,2,3,4,5) return error. whats wrong with my query ? RRS feed

  • Question

  • User-535616387 posted

    Hi Guys, 

    i got error when i use "in clause"  Conversion failed when converting the varchar value '1,2,4,5' to data type int.

    SELECT * FROM Material_List M WHERE M.ID = 4985 and M.line IN('1,2,3,4,5')

    please help whats wrong with my query ?
    the column id & line datatype is "INT"

    Thankyou & Best Regards,
    Wibowo Wiwit.

    Thursday, June 13, 2019 1:37 AM

Answers

  • User-2082239438 posted

    You will need to use the custom split function to get result.

    ----------------------CREATE THIS CUSTOM FUNCTION
    CREATE FUNCTION [dbo].[SplitString]
    (
    	@String varchar(8000), 
    	@Delimiter char(1)
    )
    RETURNS @temptable TABLE 
    (
    	ID INT,
    	DataVal VARCHAR(100)
    	
    )
    as     
    begin     
    	declare @idx int
    	declare @i int=1
    	declare @slice varchar(8000)     
        
    	select @idx = 1     
    		if len(@String)<1 or @String is null  return     
        
    	while @idx!= 0     
    	begin     
    		set @idx = charindex(@Delimiter,@String)     
    		if @idx!=0     
    			set @slice = left(@String,@idx - 1)     
    		else     
    			set @slice = @String     
    		
    		if(len(@slice)>0)
    		BEGIN
    			insert into @temptable(DataVal,ID) values(@slice,@i)
    			SET @i+=1
    		END
    
    		set @String = right(@String,len(@String) - @idx)     
    		if len(@String) = 0 break     
    	end 
    return     
    end
    GO
    ------------------RUN QUERY------------------------------------
    
    SELECT DataVal FROM dbo.SplitString('1,2,3,4,5',',')
    ----------OUTPUT
    --ID
    --1
    --2
    --3
    --4
    --5
    
    
    GO
    
    ------------------NOW CHECK YOUR QUERY USING THE SPLIT FUNCTION------------------------------------
    
    SELECT * 
    FROM Material_List M 
    WHERE M.ID = 4985 
    and M.line IN (SELECT DataVal FROM dbo.SplitString('1,2,3,4,5',','))

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 13, 2019 3:27 AM

All replies

  • User-2082239438 posted

    You will need to use the custom split function to get result.

    ----------------------CREATE THIS CUSTOM FUNCTION
    CREATE FUNCTION [dbo].[SplitString]
    (
    	@String varchar(8000), 
    	@Delimiter char(1)
    )
    RETURNS @temptable TABLE 
    (
    	ID INT,
    	DataVal VARCHAR(100)
    	
    )
    as     
    begin     
    	declare @idx int
    	declare @i int=1
    	declare @slice varchar(8000)     
        
    	select @idx = 1     
    		if len(@String)<1 or @String is null  return     
        
    	while @idx!= 0     
    	begin     
    		set @idx = charindex(@Delimiter,@String)     
    		if @idx!=0     
    			set @slice = left(@String,@idx - 1)     
    		else     
    			set @slice = @String     
    		
    		if(len(@slice)>0)
    		BEGIN
    			insert into @temptable(DataVal,ID) values(@slice,@i)
    			SET @i+=1
    		END
    
    		set @String = right(@String,len(@String) - @idx)     
    		if len(@String) = 0 break     
    	end 
    return     
    end
    GO
    ------------------RUN QUERY------------------------------------
    
    SELECT DataVal FROM dbo.SplitString('1,2,3,4,5',',')
    ----------OUTPUT
    --ID
    --1
    --2
    --3
    --4
    --5
    
    
    GO
    
    ------------------NOW CHECK YOUR QUERY USING THE SPLIT FUNCTION------------------------------------
    
    SELECT * 
    FROM Material_List M 
    WHERE M.ID = 4985 
    and M.line IN (SELECT DataVal FROM dbo.SplitString('1,2,3,4,5',','))

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 13, 2019 3:27 AM
  • User77042963 posted

    If you are using SQL Server 2016 or above, you can use the string_split function.

    Friday, June 14, 2019 1:44 PM