simple way to select an int column with values in a string

Hi,

I'd like an efficient way to do the following please

--pass a string into a stored proc eg
productids = '1,2,3,4'
-- select data where an int column matches 1+ of the productids
select *
from products where productid in productids

Cheers
louise_8Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
appariConnect With a Mentor Commented:
try

select * from products where ',' + @productids + ',' like '%,' + convert(varchar, productid ) + ',%'
0
 
deviprasadgCommented:
DECLARE @productids VARCHAR(4000) 

SET @productids = '1,2,3,4'

Declare @x XML   

select @x = cast('<A>'+ replace(@productids,',','</A><A>')+ '</A>' as xml) 
        
select t.value('.', 'int') + 1 as productids 
from @x.nodes('/A') as x(t) 

select *
from products where productid in (
select t.value('.', 'int') + 1 as productids 
from @x.nodes('/A') as x(t) 
)

Open in new window




Refer: http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx
0
 
mimran18Commented:
There are two appraches you can do it

1st  through string concatenation.
2nd through x query.

Here is the solution.

http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
harshada_sonawaneCommented:
try this


DECLARE  @productids  nvarchar(100)='1,2,3'
SELECT * FROM products  where  @productids  like '%,'+convert(varchar,productid )+',%'
OR @productids  like convert(varchar,productid )+',%' OR @productids  like '%,'+convert(varchar,productid )
OR @productids =convert(varchar,productid )
0
 
Scott PletcherSenior DBACommented:
None of those methods are efficient.

If you could have a long list of values, you need to split the string into separate values into an indexed table, then do an INNER JOIN on that table.

For a short list -- say max of ~20 values -- you can use the split directly w/o going to a temp table.

Below is the split function, then how to use it.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
RETURNS TABLE WITH SCHEMABINDING
AS
--sample use:
--SELECT * FROM (SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string) AS test_values CROSS APPLY dbo.SplitStringIntoTable(test_values.string, '/')
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
),
cteStart(num1) AS ( --This returns num + 1 (starting position of each "item" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS id,
       SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)) AS value
FROM cteStart s;
GO



declare @productids varchar(8000)
set @productids = '1,2,3,4'

create table #productids (
    productid int primary key
)

insert into #productids
select distinct pids.value
from [dbo].[SplitStringIntoTable] (@productids, ',') AS pids
order by 1


select p.*
from products p
inner join #productids pids on
    pids.productid = p.productid
0
 
louise_8Author Commented:
perfect, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.