Solved

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

Posted on 2013-01-29
6
290 Views
Last Modified: 2013-01-30
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
0
Comment
Question by:louise_8
6 Comments
 
LVL 39

Accepted Solution

by:
appari earned 90 total points
Comment Utility
try

select * from products where ',' + @productids + ',' like '%,' + convert(varchar, productid ) + ',%'
0
 
LVL 10

Expert Comment

by:deviprasadg
Comment Utility
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
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Expert Comment

by:harshada_sonawane
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Closing Comment

by:louise_8
Comment Utility
perfect, thanks
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now