September 14, 2011 at 8:41 am
Have a table with the following items:
grp_keygrp_nameClient_List (varchar (80))
10007Group19
10008Group 23029
10009 Group Combo'9','3029'
I have seen the in operator used in the following:
Select column from table where Client_List in ('9','3028')
However, the code I'm debugging from a previous colleague uses in differently:
select column from table where '9' in (Client_List)
This only returns the row with just 9 as the value in Client_List.
However, the code expects it to find the 2 rows 9 and 9,3028.
Thoughts on how to write a select statement to find the 2 rows?
September 14, 2011 at 9:09 am
select column from table where Client_List like '9%'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2011 at 9:15 am
Unless your data actually contains the single quotes. Here is sample data to see if this can help fit your needs.
create table #Temp
(
grp_key int,
grp_name varchar(25),
Client_List varchar (80)
)
insert #Temp (grp_key, grp_name, Client_List)
values
(10007, 'Group1', '9'),
(10008, 'Group 2', '3029'),
(10009, 'Group Combo', '''9'',''3029''')
select * from #Temp
where replace(Client_List, '''', '') like '9%'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2011 at 9:39 am
Thanks.
However if client_list contains the value 99,3014,118,196,163,131,185,191,62,18,101,81,3007
doing a like 9% would return this row, when it would not be valid?
Also would return a row with a value of 95. Again not valid.
September 14, 2011 at 9:43 am
tkatona-1119058 (9/14/2011)
Thanks.However if client_list contains the value 99,3014,118,196,163,131,185,191,62,18,101,81,3007
doing a like 9% would return this row, when it would not be valid?
Also would return a row with a value of 95. Again not valid.
And that is why is bad practice to store multiple values in a single datafield.;-) You are going to have to parse your data. Is your data consistent? You presented one with '9', '2039' and this one with the ' marks. Either way you still have to parse this to get your desired results. Take a look at Jeff Moden's article about the best approach to parsing this type of data here[/url].
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2011 at 10:08 am
If your comma-separated lists are uniform, then you could search them like this
select * from #Temp
where ',' + Client_List + ',' like '%,9,%'
But only you can know if it will work for your data. It won't work in your original post, for example.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply