Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-04-30 : 13:57:51
|
| John Adams writes "Trying to use value of field that contains a comma delimited string as the "IN" criterion.Here's the idea:SELECT Owner_tbl.L_Name FROM Owner_tbl WHERE ID_Owner IN(SELECT Team_Members2 FROM Team_tbl WHERE ID_Team = 100)The return from the nested query would be like this: 22, 33, 44If I remove the nested query and simply put in the string (22, 33, 44) it works as expected. But using the nested query I get error:Syntax error converting the varchar value '22, 33, 44' to a column of data type int.ID_Owner is an int, however the '22, 33, 44' is a comma delimited string stored in Team_tbl.Team_Members2 (varchar)Apparently, the returned value of the nested query is enclosed in quotes since if I use the quoted string in the simple query [IN('22, 33, 44')] then I get the same error.My question:Is there a way to get rid of the "'" quotes in the return from the nested query... or simply... a way to make this work?" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-30 : 14:33:46
|
| SELECT Owner_tbl.L_Name FROM Owner_tbl WHERE ',' + replace((SELECT Team_Members2 FROM Team_tbl WHERE ID_Team = 100),' ','') + ',' like '%,' + convert(varchar(10),ID_Owner) + ',%' ==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Nested Query and comma delimited value