Easy way to include a list without having to put quotes around each item separated by comma?

Toad for Oracle & DevOps Toolkit
1

I know it’s not the most tedious task, but when querying with a big list as a filter (the “in” statement), sometimes it’s a pain to use the “find and replace” function for this.

Is there any way to format the query or change some sort of setting where I can just have a long list? In MS Word, I have a macro to do this, but the quote symbol in MS Word is slightly different.

For example, if I want to query all employees in these 20 departments at our company, i would put

select employee_name

from EmployeeDatabase]

where department_number in (1234, 4286, 3486, 2346, 2348, 1298, 2143) <— I have to put quotes around each number.

Thanks!

2

Evening,

You can still use your Word macro provided you delve into the settings and turn off so called "smart quotes".

If I remember, you do it in two places, autocorrect and something while you type.

Then edit the macro to make sure you are using the vanilla single quote and not a smart one.

HTH

Cheers,

Norm [ TeamT ]

On 20 November 2014 20:35:25 GMT+00:00, Kennon123 bounce-Kennon123@toadworld.com wrote:

Easy way to include a list without having to put quotes around each item separated by comma?

Thread created by Kennon123
I know it's not the most tedious task, but when querying with a big list as a filter (the "in" statement), sometimes it's a pain to use the "find and replace" function for this.

Is there any way to format the query or change some sort of setting where I can just have a long list? In MS Word, I have a macro to do this, but the quote symbol in MS Word is slightly different.

For example, if I want to query all employees in these 20 departments at our company, i would put

select employee_name

from EmployeeDatabase]

3

Search the help info for “Find and replace text”.

It shows how to create two macros to wrap text and make it comma separated. Once you create the macros… two clicks and you’re done. Saves a ton of time with
long lists.

From: Norm [TeamT] [mailto:bounce-NormTeamT@toadworld.com]

Sent: Thursday, November 20, 2014 3:13 PM

To: toadoracle@toadworld.com

Subject: Re: [Toad for Oracle - Discussion Forum] Easy way to include a list without having to put quotes around each item separated by comma?

Re:
Easy way to include a list without having to put quotes around each item separated by comma?

Reply by Norm [TeamT]

Evening,

You can still use your Word macro provided you delve into the settings and turn off so called “smart quotes”.

If I remember, you do it in two places, autocorrect and something while you type.

Then edit the macro to make sure you are using the vanilla single quote and not a smart one.

HTH

Cheers,

Norm [ TeamT ]

On 20 November 2014 20:35:25 GMT+00:00, Kennon123 bounce-Kennon123@toadworld.com wrote:

Easy
way to include a list without having to put quotes around each item separated by comma?

Thread created by Kennon123

I know it’s not the most tedious task, but when querying with a big list as a filter (the “in” statement), sometimes it’s a pain to use the “find and replace” function for this.

Is there any way to format the query or change some sort of setting where I can just have a long list? In MS Word, I have a macro to do this, but the quote symbol in MS Word is slightly different.

For example, if I want to query all employees in these 20 departments at our company, i would put

select employee_name

from EmployeeDatabase]

To reply, please reply-all to this email.

Stop
receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

4

That topic is a little outdated too. You can edit your replace macros and include both of those shown in the help as a single macro instead of having to execute each independently.

On 11/21/2014 09:35 AM, crjohnso wrote:

RE: Easy way to include a list without having to put quotes around each item separated by comma?

Reply by crjohnso
Search the help info for "Find and replace text".

It shows how to create two macros to wrap text and make it comma separated. Once you create the macros.... two clicks and you're done. Saves a ton of time with long lists.

From: Norm [TeamT] [mailto:bounce-NormTeamT@toadworld.com]

Sent: Thursday, November 20, 2014 3:13 PM

To:
toadoracle@toadworld.com

Subject: Re: [Toad for Oracle - Discussion Forum] Easy way to include a list without having to put quotes around each item separated by comma?

Re:
Easy way to include a list without having to put quotes around each item separated by comma?

Reply by Norm [TeamT]

Evening,

You can still use your Word macro provided you delve into the settings and turn off so called "smart quotes".

If I remember, you do it in two places, autocorrect and something while you type.

Then edit the macro to make sure you are using the vanilla single quote and not a smart one.

HTH

Cheers,

Norm [ TeamT ]

On 20 November 2014 20:35:25 GMT+00:00, Kennon123 bounce-Kennon123@toadworld.com wrote:

Easy
way to include a list without having to put quotes around each item separated by comma?

Thread created by Kennon123

I know it's not the most tedious task, but when querying with a big list as a filter (the "in" statement), sometimes it's a pain to use the "find and replace" function for this.

Is there any way to format the query or change some sort of setting where I can just have a long list? In MS Word, I have a macro to do this, but the quote symbol in MS Word is slightly different.

For example, if I want to query all employees in these 20 departments at our company, i would put

select employee_name

from EmployeeDatabase]

To reply, please reply-all to this email.

Stop
receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

Thank you, I had the same need and this was perfect! Once in the help, I had to select topic named “Replace with template” and it had detailed example just exactly how you described. And as Michael Staszewski noted, you can use the Advanced macro editor feature and create a macro group, which will be a sequence of editor macros executed in order.

Kennon123: you should mark this as the answer.