# String of strings **URL:** \ **Category:** ruby-talk\ **Created:** [19 December 2006 08:25 UTC](https://rubytalk.org/t/string-of-strings/33853 "2006-12-19T08:25:16Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Josselin](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@Josselin](https://rubytalk.org/u/Josselin)\ **Post date:** [19 December 2006 08:25 UTC](https://rubytalk.org/t/string-of-strings/33853/1 "2006-12-19T08:25:16Z")
I got an array of id's : ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245] I would like to get a string of strings (to be used in SQL WHERE..IN clause " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' " If a use ids.join(','), I get "22443,22468,22371,22218,22472,22377,22245" which is obviously not useful for SQL.... is there any shorcuts or should I concatenate each element ? thanks joss ---
**Author:** ![Martin\_DeMello](https://avatars.discourse-cdn.com/v4/letter/m/f17d59/32.png) [@Martin\_DeMello](https://rubytalk.org/u/Martin_DeMello)\ **Post date:** [19 December 2006 08:29 UTC](https://rubytalk.org/t/string-of-strings/33853/2 "2006-12-19T08:29:38Z")
ids.map {|i| "'#{i}'"}.join(" ,") martin > **···** > > On 12/19/06, Josselin \ wrote: > > > I got an array of id's : > > > > ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245] > > > > I would like to get a string of strings (to be used in SQL WHERE..IN clause > > > > " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' " > > > > If a use ids.join(','), I get > > "22443,22468,22371,22218,22472,22377,22245" which is obviously not > > useful for SQL.... ---
**Author:** ![Shiwei\_Zhang](https://avatars.discourse-cdn.com/v4/letter/s/eb8c5e/32.png) [@Shiwei\_Zhang](https://rubytalk.org/u/Shiwei_Zhang)\ **Post date:** [19 December 2006 09:11 UTC](https://rubytalk.org/t/string-of-strings/33853/3 "2006-12-19T09:11:59Z")
ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245] "IN ('"+ids.join("', '")+"')" Shiwei (The views expressed are my own and not necessarily those of Oracle and its affiliates.) Josselin wrote: > **···** > > > I got an array of id's : > > > > ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245] > > > > I would like to get a string of strings (to be used in SQL WHERE..IN clause > > > > " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' " > > > > If a use ids.join(','), I get "22443,22468,22371,22218,22472,22377,22245" which is obviously not useful for SQL.... > > > > is there any shorcuts or should I concatenate each element ? > > > > thanks > > > > joss ---
**Author:** ![Johan\_Veenstra](https://avatars.discourse-cdn.com/v4/letter/j/7993a0/32.png) [@Johan\_Veenstra](https://rubytalk.org/u/Johan_Veenstra)\ **Post date:** [19 December 2006 09:09 UTC](https://rubytalk.org/t/string-of-strings/33853/4 "2006-12-19T09:09:11Z")
"'#{ids.join("','")}'" Johan > **···** > > On 12/19/06, Martin DeMello \ wrote: > > > On 12/19/06, Josselin \ wrote: > > \> I got an array of id's : > > \> > > \> ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245] > > \> > > \> I would like to get a string of strings (to be used in SQL > > WHERE..IN clause > > \> > > \> " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , > > '22245' " > > \> > > \> If a use ids.join(','), I get > > \> "22443,22468,22371,22218,22472,22377,22245" which is obviously not > > \> useful for SQL.... > > > > ids.map {|i| "'#{i}'"}.join(" ,") > > > > martin ---
**Author:** ![Josselin](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@Josselin](https://rubytalk.org/u/Josselin)\ **Post date:** [19 December 2006 09:25 UTC](https://rubytalk.org/t/string-of-strings/33853/5 "2006-12-19T09:25:06Z")
thanks Martin... I realize that the error I got was not due to integers list , "22443, 22468, 22371, 22218, 22472, 22377, 22245" is accepted... so the join(',') is ok.. but the list is too long ... for a select statement.. (I can have 50 numbers or more.....) there is a characters limit to the select joss > **···** > > On 2006-12-19 09:29:38 +0100, "Martin DeMello" \ said: > > > On 12/19/06, Josselin \ wrote: > > > > > I got an array of id's : > > > > > > ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245] > > > > > > I would like to get a string of strings (to be used in SQL WHERE..IN clause > > > > > > " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' " > > > > > > If a use ids.join(','), I get > > > "22443,22468,22371,22218,22472,22377,22245" which is obviously not > > > useful for SQL.... > > > > ids.map {|i| "'#{i}'"}.join(" ,") > > > > martin ---
**Author:** ![Robert\_K1](https://yyz1.discourse-cdn.com/flex029/user_avatar/rubytalk.org/robert_k1/32/1830_2.png) [@Robert\_K1](https://rubytalk.org/u/Robert_K1)\ **Post date:** [19 December 2006 12:00 UTC](https://rubytalk.org/t/string-of-strings/33853/6 "2006-12-19T12:00:15Z")
Maybe you can generate ranges for adjacent values. IIRC we had a thread here at some point in the past - maybe it's even a quiz. A completely different option is to create a temp table (depends on your DB whether and how that is done), put all the values in that temp table and join it against the table you are querying. Kind regards   robert > **···** > > On 19.12.2006 10:23, Josselin wrote: > > > On 2006-12-19 09:29:38 +0100, "Martin DeMello" \ \> said: > > > > > On 12/19/06, Josselin \ wrote: > > > > > > > I got an array of id's : > > > > > > > > ids = [22443, 22468, 22371, 22218, 22472, 22377, 22245] > > > > > > > > I would like to get a string of strings (to be used in SQL WHERE..IN clause > > > > > > > > " '22443' , ' 22468' , ' 22371' , ' 22218' , '22472' , '22377' , '22245' " > > > > > > > > If a use ids.join(','), I get > > > > "22443,22468,22371,22218,22472,22377,22245" which is obviously not > > > > useful for SQL.... > > > > > > ids.map {|i| "'#{i}'"}.join(" ,") > > > > > > martin > > > > thanks Martin... I realize that the error I got was not due to integers list , "22443, 22468, 22371, 22218, 22472, 22377, 22245" is accepted... so the join(',') is ok.. > > but the list is too long ... for a select statement.. (I can have 50 numbers or more.....) there is a characters limit to the select ---
**Author:** ![David\_Vallner](https://avatars.discourse-cdn.com/v4/letter/d/eb9ed0/32.png) [@David\_Vallner](https://rubytalk.org/u/David_Vallner)\ **Post date:** [21 December 2006 00:14 UTC](https://rubytalk.org/t/string-of-strings/33853/7 "2006-12-21T00:14:06Z")
Robert Klemme wrote: > A completely different option is to create a temp table (depends on your > DB whether and how that is done), put all the values in that temp table > and join it against the table you are querying. Talk about serendipity... I was looking for a way to avoid having to do either any sort of query string building (which I hate) or multiple SELECTS (for obvious reasons) for a WHERE ... IN situation for work stuff, and this one sounds quite nifty. Would the performance of that be comparable to using a WHERE ... IN? David Vallner ---
**Author:** ![Robert\_K1](https://yyz1.discourse-cdn.com/flex029/user_avatar/rubytalk.org/robert_k1/32/1830_2.png) [@Robert\_K1](https://rubytalk.org/u/Robert_K1)\ **Post date:** [21 December 2006 20:25 UTC](https://rubytalk.org/t/string-of-strings/33853/8 "2006-12-21T20:25:08Z")
Depends on the number of items in the temp table and probably also on indexing. My guess would be that it's pretty fast for some hundred entries. Note that not all RDBMS allow to have indexes on temp tables. MS SQL Server also has something called "table variable" which might give different results. Lots of options... 🙂 Kind regards   robert > **···** > > On 21.12.2006 01:14, David Vallner wrote: > > > Robert Klemme wrote: > > > > > A completely different option is to create a temp table (depends on your > > > DB whether and how that is done), put all the values in that temp table > > > and join it against the table you are querying. > > > > Talk about serendipity... I was looking for a way to avoid having to do > > either any sort of query string building (which I hate) or multiple > > SELECTS (for obvious reasons) for a WHERE ... IN situation for work > > stuff, and this one sounds quite nifty. Would the performance of that be > > comparable to using a WHERE ... IN? ---
**Author:** ![David\_Vallner](https://avatars.discourse-cdn.com/v4/letter/d/eb9ed0/32.png) [@David\_Vallner](https://rubytalk.org/u/David_Vallner)\ **Post date:** [21 December 2006 21:02 UTC](https://rubytalk.org/t/string-of-strings/33853/9 "2006-12-21T21:02:22Z")
Robert Klemme wrote: > > Robert Klemme wrote: > > > > > A completely different option is to create a temp table (depends on your > > > DB whether and how that is done), put all the values in that temp table > > > and join it against the table you are querying. > > > > Talk about serendipity... I was looking for a way to avoid having to do > > either any sort of query string building (which I hate) or multiple > > SELECTS (for obvious reasons) for a WHERE ... IN situation for work > > stuff, and this one sounds quite nifty. Would the performance of that be > > comparable to using a WHERE ... IN? > > Depends on the number of items in the temp table and probably also on > indexing. My guess would be that it's pretty fast for some hundred > entries. Note that not all RDBMS allow to have indexes on temp tables. > MS SQL Server also has something called "table variable" which might > give different results. Lots of options... 🙂 > > Kind regards > >     robert I don't really need an index, it's a throwaway temp table. I just want to avoid either handcoding parameter escaping or making a parameterised query using "WHERE foo IN (?,?,?, [...] ?)" and setting those I have an unrational fear of looping over integers; argh pure JDBC apps, yay lack of common sense and using an API designed to do the lowest level of abstraction for application programming. The query is rather small, probably in the order of tens of items at best, so a batch insert into the temp table and then the join shouldn't hurt performance over the hackish method and still end up better than doing selects one by one because of the reduced lag. Although my hunch is what's killing that one is that it involves basically a join on (unindexed) varchar columns, so I don't expect miracles 😜 Now, if only this weren't Oracle 8i that only supports that weird "predefined table for temporary data" concept instead of true transaction-scoped temp tables... \*sigh\* David Vallner > **···** > > > On 21.12.2006 01:14, David Vallner wrote: ---
**Author:** ![Sam\_Smoot](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@Sam\_Smoot](https://rubytalk.org/u/Sam_Smoot)\ **Post date:** [22 December 2006 00:35 UTC](https://rubytalk.org/t/string-of-strings/33853/10 "2006-12-22T00:35:10Z")
Keep in mind that temp-tables in MSSQL are still written to disk in TempDB. So as a general rule, if you're concerned about performance, if you don't \*need\* a temp-table, don't use it. Not that performance should always be a #1 concern of course. Just that I've seen hundreds if not thousands of stored-procedures that use temp-tables as a matter of course just because the developer wasn't comfortable with sub-selects, grouping, etc. I'm sure you won't fall into that trap though. 🙂 There are also in-memory tables, but I don't remember the caveats. I think perhaps they might have a global scope or something inconvienent like that, but don't quote me on that. -Sam David Vallner wrote: > **···** > > > Robert Klemme wrote: > > \> On 21.12.2006 01:14, David Vallner wrote: > > \>\> Robert Klemme wrote: > > \>\>\> A completely different option is to create a temp table (depends on your > > \>\>\> DB whether and how that is done), put all the values in that temp table > > \>\>\> and join it against the table you are querying. > > \>\> > > \>\> Talk about serendipity... I was looking for a way to avoid having to do > > \>\> either any sort of query string building (which I hate) or multiple > > \>\> SELECTS (for obvious reasons) for a WHERE ... IN situation for work > > \>\> stuff, and this one sounds quite nifty. Would the performance of that be > > \>\> comparable to using a WHERE ... IN? > > \> > > \> Depends on the number of items in the temp table and probably also on > > \> indexing. My guess would be that it's pretty fast for some hundred > > \> entries. Note that not all RDBMS allow to have indexes on temp tables. > > \> MS SQL Server also has something called "table variable" which might > > \> give different results. Lots of options... 🙂 > > \> > > \> Kind regards > > \> > > \> robert > > \> > > > > I don't really need an index, it's a throwaway temp table. I just want > > to avoid either handcoding parameter escaping or making a parameterised > > query using "WHERE foo IN (?,?,?, [...] ?)" and setting those I have an > > unrational fear of looping over integers; argh pure JDBC apps, yay lack > > of common sense and using an API designed to do the lowest level of > > abstraction for application programming. The query is rather small, > > probably in the order of tens of items at best, so a batch insert into > > the temp table and then the join shouldn't hurt performance over the > > hackish method and still end up better than doing selects one by one > > because of the reduced lag. Although my hunch is what's killing that one > > is that it involves basically a join on (unindexed) varchar columns, so > > I don't expect miracles 😜 > > > > Now, if only this weren't Oracle 8i that only supports that weird > > "predefined table for temporary data" concept instead of true > > transaction-scoped temp tables... \*sigh\* > > > > David Vallner > > > > --------------enig8B737F04AD777652669F493C > > Content-Type: application/pgp-signature > > Content-Disposition: inline; > >   filename="signature.asc" > > Content-Description: OpenPGP digital signature > > X-Google-AttachSize: 188 ---
**Author:** ![David\_Vallner](https://avatars.discourse-cdn.com/v4/letter/d/eb9ed0/32.png) [@David\_Vallner](https://rubytalk.org/u/David_Vallner)\ **Post date:** [22 December 2006 12:25 UTC](https://rubytalk.org/t/string-of-strings/33853/11 "2006-12-22T12:25:07Z")
Sam Smoot wrote: > Keep in mind that temp-tables in MSSQL are still written to disk in > TempDB. So as a general rule, if you're concerned about performance, if > you don't \*need\* a temp-table, don't use it. Well, the Powers That Be said "it's probably the query lag". So this should clear it. My opinion is that it's the varchar column that could use a unique index constraint, and since it's a read-only rather small table (in the order of tens of records, maybe hundreds at most) from the POV of the client app I'm working on, I'd prefer to just prefetch it on startup and stop fooling around. > Not that performance should always be a #1 concern of course. Just that > I've seen hundreds if not thousands of stored-procedures that use > temp-tables as a matter of course just because the developer wasn't > comfortable with sub-selects, grouping, etc. I'm sure you won't fall > into that trap though. 🙂 Erm. Using a temp table to store data that -already is- in the DB? Eugh. I presume that's the same kind of developer that's not comfortable with nesting function calls and gets into 9 levels of indentation and umpty local variables. And if I have the necessary privileges on a DB, any and all subselects I see are very good candidates to be axed and put into a view. > There are also in-memory tables, but I don't remember the caveats. I > think perhaps they might have a global scope or something inconvienent > like that, but don't quote me on that. This is Oracle 8i, working in Mysterious Ways (tm), and the only temp tables you get are globally scoped with locally scoped data. Luckily I think developers have create table rights, so it should be possible to sneak this in. Right, thanks for the hints! David Vallner ---
**Author:** ![Robert\_K1](https://yyz1.discourse-cdn.com/flex029/user_avatar/rubytalk.org/robert_k1/32/1830_2.png) [@Robert\_K1](https://rubytalk.org/u/Robert_K1)\ **Post date:** [22 December 2006 16:30 UTC](https://rubytalk.org/t/string-of-strings/33853/12 "2006-12-22T16:30:09Z")
> Keep in mind that temp-tables in MSSQL are still written to disk in > TempDB. So as a general rule, if you're concerned about performance, if > you don't \*need\* a temp-table, don't use it. The fact that they reside in tempdb does not necessarily mean they are written to disk or that they are slow. Small temp tables will easily fit into the page cache. And since they are created directly before usage likelihood of finding those pages in the cache is pretty high. Also, tempdb has recovery model simply which reduces burden on the disk somewhat. Also, I am not sure whether operations on temp tables are logged at all. > Not that performance should always be a #1 concern of course. Just that > I've seen hundreds if not thousands of stored-procedures that use > temp-tables as a matter of course just because the developer wasn't > comfortable with sub-selects, grouping, etc. I'm sure you won't fall > into that trap though. 🙂 Yeah, that's what I heard also: often people use temp tables because they do not know SQL and the capabilities of their RDBMS very well. > There are also in-memory tables, but I don't remember the caveats. I > think perhaps they might have a global scope or something inconvienent > like that, but don't quote me on that. They are called "table variables". Scope is not an issue, they are scoped like local variables (unless maybe if they are returned from a procedure). IIRC limitation is that they do not allow indexes and constraints. Kind regards   robert PS: Please don't top post. > **···** > > On 22.12.2006 01:34, Sam Smoot wrote: ---
**Author:** ![Sam\_Smoot](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@Sam\_Smoot](https://rubytalk.org/u/Sam_Smoot)\ **Post date:** [22 December 2006 21:00 UTC](https://rubytalk.org/t/string-of-strings/33853/13 "2006-12-22T21:00:05Z")
> The fact that they reside in tempdb does not necessarily mean they are > written to disk or that they are slow. Small temp tables will easily > fit into the page cache. And since they are created directly before > usage likelihood of finding those pages in the cache is pretty high. > Also, tempdb has recovery model simply which reduces burden on the disk > somewhat. Also, I am not sure whether operations on temp tables are > logged at all. I think you've got it right about whether a temp-table \_must\_ be written to disk during it's lifetime. Looking it up just now I found conflicting accounts, so I can't authoritatively say either way. :-/ > \> There are also in-memory tables, but I don't remember the caveats. I > \> think perhaps they might have a global scope or something inconvienent > \> like that, but don't quote me on that. > > They are called "table variables". Scope is not an issue, they are > scoped like local variables (unless maybe if they are returned from a > procedure). IIRC limitation is that they do not allow indexes and > constraints. It looks like I was actually thinking about global temp-tables using the ##table syntax. Shows how often I've found a use for them I suppose. 🙂 > PS: Please don't top post. Sorry about that. :-o