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 |
|
waveform
Yak Posting Veteran
93 Posts |
 Posted - 2014-03-20 : 06:52:34
|
| A table I'm working with has a varchar column containing a comma-delimited string of numbers, which match up to smallint codes in another table. I gather this is someone's implementation of a many-to-many relationship. :)Using SQL Server 2008 and wondering if there's a special trick to using a string list of numbers with IN()? Someone suggests [url=http://www.codeproject.com/Tips/584680/Using-comma-separated-value-parameter-strings-in-S]converting the string into a temp table[/url] but I'd just like to make sure there isn't a quicker, easier approach, or if that's it. Many thanks!(ed: sorry can't work out how to do those links properly) |
|
|
VeeranjaneyuluAnnapureddy
Posting Yak Master
169 Posts |
Posted - 2014-03-20 : 07:38:47
|
| DECLARE @Id VARCHAR(100) = '25,187'select * FROM YourTableNameWHERE PATINDEX('%,'+CAST(Id AS VARCHAR(100))+',%',','+@Id+',')>0Veera |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-03-20 : 12:25:56
|
| You can use a solution like Veera posted using PATINDEX or a LIKE or what have you. If this is just a one-off query, then no biggie. But, if this is real code (read production). Then I'd try to fix the table structure. Alternatively, you could use a string parsing function similar to the link you posted. I'd have to dig up my performance numbers, but I seem to recall that the string parsing function is faster but, perhaps more importantly, it's more readable. If you need a good split/parse function I'd use this one:http://www.sqlservercentral.com/articles/Tally+Table/72993/ |
|
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2014-03-20 : 13:44:33
|
| Thanks very much for those suggestions! Yep, it'll be in production, but thankfully not hit very often. Changing to a proper many-to-many structure is definitely preferred, but not up to me in this case. :) |
|
|
|
|
|
|
|
|
Using a string list, eg. "1,2,3", with IN()