July 1, 2009 at 2:11 pm
I want to select a range of identity numbers (PK) like this
SET @select = '1,10,5,15,18,20'
Note:
1) the ID field has numeric numbers from 1 to 100+
2) I need the variable to be like this '1,10,5,15,18,20' or of similar nature. Multi select.
This code..
SELECT [ID]
FROM [myTable]
WHERE CHARINDEX(LTRIM(RTRIM([ID])),'1,10,5,15,18,20',1) > 0
Produces this result
1
2
5
8
10
15
18
20
ERROR : 2 should and 8 should not be there...
How can I work round this with doing a loop either in client side or server side ?
Thanks
UPDATE:
This works fine:
SELECT [ID]
FROM [myTable]
WHERE [ID] IN (1,10,5,15,18,20)
BUt how do I pass this into a stored proc variable : 1,10,5,15,18,20
This does not work : @select = '1,10,5,15,18,20'
?????
July 1, 2009 at 2:39 pm
Hi Digs,
a couple questions/comments:
- Why are you using T-SQL to get the same results as you provided as input data?
- T-SQL can use the IN clause, but there are better options to use, depending on the requirement.
- What is the goal you're trying to achieve? Maybe a split string function is the better way to use. Depending on the structure of the string to be split there seems to be different solutions providing best performance. But as a "good way to start with" I'd recommend you read the following article:
Jeff Moden's Tally Table[/url]. This article also covers your scenario as far as I can tell.
If you'd like us to provide you with a more detailed sample code please help us by providing sample data in a ready to use format. Please follow the link in my signature on ho to post data for fast answers.
Example for IN clause:
SELECT [ID]
FROM [myTable]
WHERE [ID] IN (1,10,5,15,18,20)
July 1, 2009 at 2:50 pm
found a solution here
July 1, 2009 at 3:01 pm
Okay, I'll take the challenge. Code attached.
July 1, 2009 at 3:12 pm
First thought: Glad you found a solution.
But when I took a look at the link you posted a WHILE loop stared at me... :crying:
This is usually not considered to be one of the most efficient ways for a split string function.
Depending on the way you're using the split function you might see increasing performance issues.
If that is going to happen, you should have the link ready I posted above (Jeff's article)...- or the sample code you just got from Lynn. 😉
I cannot force you to avoid the loop but I have to let you know about the flip side.
The only way to show you that there are better way is providing sample code - just done by Lynn. 🙂
July 1, 2009 at 3:39 pm
why cant you do something like this?
you know you have comma between every value, but its the first and last value that dont have start and end comma, so lets add a comma at the start and end like
Select @select=','+@select+','
and run your CharIndex, for example
Declare
@select Varchar(8000)
SET @select = '1,10,5,15,18,20'
Select @select=','+@select+','
SELECT[ID]
FROM[myTable]
WHERECHARINDEX(','+LTRIM(RTRIM([ID]))+',',@select,1) > 0
July 1, 2009 at 3:48 pm
rijiboy (7/1/2009)
why cant you do something like this?you know you have comma between every value, but its the first and last value that dont have start and end comma, so lets add a comma at the start and end like
Select @select=','+@select+','
and run your CharIndex, for example
Declare
@select Varchar(8000)
SET @select = '1,10,5,15,18,20'
Select @select=','+@select+','
SELECT[ID]
FROM[myTable]
WHERECHARINDEX(','+LTRIM(RTRIM([ID]))+',',@select,1) > 0
I haven't tried it, but I can tell you that if it works, it is most likely not very scalable. It won't make use of an index if one exists on ID and will result in a table scan. Not too bad for a small tables, but start adding more rows and you will see a significant decrease in performance.
July 1, 2009 at 3:54 pm
Lynn,
you are absolutely right, it will not, but the original question was not about speed 😀
Riz
July 1, 2009 at 3:57 pm
rijiboy (7/1/2009)
Lynn,you are absolutely right, it will not, but guess that was not the initial question txtPost_CommentEmoticon(':-D');
Riz
True, but sometimes you need to think what else may a individual use your suggestion for in the future. If I can provide a scalable solution, then that is the better course of action.
July 1, 2009 at 3:59 pm
Lynn nice code.. thanks:-D:-)
July 1, 2009 at 4:05 pm
hang on..
I assume DelimitedSplit.txt can handle 1, 5, to 50 or 80 IDs at a time ??
July 1, 2009 at 4:15 pm
Digs (7/1/2009)
hang on..I assume DelimitedSplit.txt can handle 1, 5, to 50 or 80 IDs at a time ??
DelimitedSplit.txt , no. That is just the code for the function dbo.DelimitedSplit. 😉
Why not take it for spin? Increase the size of the string you want to split.
July 1, 2009 at 4:29 pm
Increase the size of the string.. I assume that means what I thought
String of different sizes
'1,2,3'
or
'1'
or
'1,2,3,5,6,7,9 ...etc to... 89,90,91,92'
So 92 IDs in the string, that would work also would it not?
July 1, 2009 at 4:35 pm
Digs (7/1/2009)
Increase the size of the string.. I assume that means what I thoughtString of different sizes
'1,2,3'
or
'1'
or
'1,2,3,5,6,7,9 ...etc to... 89,90,91,92'
So 92 IDs in the string, that would work also would it not?
Try it: select * from dbo.DelimitedSplit(@string,',') -- where @string contains as big a string as you wish to create
July 1, 2009 at 4:40 pm
..Try It..
I am but my TEST data set only allows 30 or so selections..
Are you saying you are not sure if it can handle 100+ selections????
Please dont be cyptic, thanks ::hehe:
Viewing 15 posts - 1 through 15 (of 70 total)
You must be logged in to reply to this topic. Login to reply