Oracle SQL developer - What is the easiest way to surround a list of texts with quotes and commas?-CodePudding

Here's an example. I get the list like this below


99X2H19JBF11534
16B4H5FHCA12592
ABE4H5FHAA08646
SE0753207527
PAK810869145
PAK810714143
PAK810547887
PAK810340854
PAK820090918

I then use concatenate in excel (and surround the list with quotes and commas) to run queries like below in Oracle SQL Developer.

select * from admin.repair

where repair_nbr in (

'99X2H19JBF11534',
'16B4H5FHCA12592',
'ABE4H5FHAA08646',
'SE0753207527',
'PAK810869145',
'PAK810714143',
'PAK810547887',
'PAK810340854',
'PAK820090918');

I was wondering if other people have an easier way to surround the list with quotes and commas in Oracle SQL developer.

CodePudding user response：

If you got your list from a query, just drag and drop.

We'll not only quote the strings, we'll separate them with commas for you.

If you just have the text, SQL Developer also support block editing. Enable that on the Edit menu, then simply use down arrow to go through each line/curpos and select then paste the quotes.

Or if you're a fan of regex, the Search and Replace (ctrl R) panel has a RegEx mode you can toggle on.

Disclaimer: I'm a product manager at Oracle for SQL Developer.

CodePudding user response：

Assuming you got your list using query

select val from your_table order by whatever

change your query to

select 'select * from admin.repair where repair_nbr in ('
       || listagg('''' || val || '''', ',
' order by whatever)
       || ');'
from your_table

The result of auxiliar query is the query you require.

Note: You might get ORA-01489 if the result of concatenation is too long but it is different - yet still solvable - story (look for xmlagg function then).