Workaround inside IN OPERATOR

Question

ALERT
ALERTING_TRANSACTION
ALERTING_TRANSACTION_LINK
ALERT_ACTION_LOG
ALERT_ALERT
ALERT_ASSIGNMENT
ALERT_CASE
ALERT_CUSTOMER
ALERT_DOCUMENT
ALERT_EXT
ALERT_MATCH
ALERT_MATCH_DATA
ALERT_RESTRICTION
ALERT_STATUS
ALERT_TRANSACTION
A_ALERT
A_ALERTING_TRANSACTION
A_ALERT_ACTION_LOG
A_ALERT_ASSIGNMENT
A_ALERT_CASE
A_ALERT_MATCH
A_ALERT_MATCH_DATA
AUDIT_LOGS
CASES
CASE_ACTION_LOG
CASE_CUSTOMER
CASE_DIARY
CASE_DOCUMENT
CASE_EXT
CASE_NARRATIVE
CASE_TRANSACTION
CHANNEL_SUBSCRIPTIONS
COUNTRY
CURRENCY
CUSTOMERS
CUSTOMERS_RANGE
CUSTOMER_AUDIT_LOG
CUSTOMER_PROFILE
CUSTOMER_STATUSES
CUSTOM_TRANSACTION_FIELD
DF_ACCOUNT
DF_ACTIVITY_TYPE
DF_ASSISTANCE_CONTACT
DF_AUTH_SIGN_PART
DF_CHECKBOX_FINTRAC
DF_CUSTOMER
DF_DISC
DF_DISCLOSURE_AUSTRAC_SMR
DF_DISCLOSURE_CANFINTRAC_31
DF_DISCLOSURE_LUXEMBOURGSTR
DF_DISCLOSURE_USFINCEN_109
DF_DISC_TYPE
DF_DISC_VERSION
DF_EFILE_BATCH
DF_FINTRAC_INSTITUTE
DF_HONGKONG_INSTITUTION
DF_INSTITUTION_AUSTRAC
DF_LAW_ENFORCEMENT_AGENCY
DF_NARRATIVE
DF_REGULATORY_CONTACT

I have these records in a text file. I want to write a query like

SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME NOT IN ('ALERT','ALERTING_TRANSACTION',.....,'DF_REGULATORY_CONTACT')

Can anybody tell me how can I add '' around each table name and separate the table_names by , inside the IN OPERATOR as the list of tables is veryyyyyy long.

Are you trying to manipulate the text file to create a SQL statement? Could you load the data into a table in the database so that your `NOT IN` could query that table rather than a hard-coded list? What constitutes "very long"-- an IN list is limited to 1000 elements if you're hard-coding values. — Justin Cave, Nov 14 '11 at 17:41
Is this a one-off exercise (you only want to run the query once)? Or do you want to create a fixed query (view, stored procedure) with a hard-coded list of names? Or is the lisyt likely to vary each time you run the query? Plus, what are the constraints? Can you create database objects? — APC, Nov 14 '11 at 17:47
I ask all those questions because on teh face of it, this is a trivial "how do I edit a text file?" question. — APC, Nov 14 '11 at 17:48
FYI - for context: http://stackoverflow.com/questions/8124206/need-a-query-to-get-a-list-of-used-and-unused-tables — Code Magician, Nov 14 '11 at 17:49
@APC I had to run it only once. The whole premise behind it is to get the tables where the data was changed before they (our client) launch the project. — Bhushan, Nov 14 '11 at 17:58

amit_g · Accepted Answer · 2011-11-14T17:59:35.470

2

Use your favorite text editor to replace line begin (^ in regular expression) with ' and line end ($ in regular expression) with ',. Remove the last , and you have a list to put inside the ( ).

EDIT

If your favorite text editor doesn't support regular expression, get one that does. It would be great addition to your tool list (for one off such as this) even if you continue to use your favorite text editor that doesn't support regular expression for day-today text editing.

edited Nov 14 '11 at 17:59

answered Nov 14 '11 at 17:41

amit_g

30,880
8
61
118

Worth mentioning that not every text editor does support Regex. Whether a programmer should have such a tool as their favourite is a matter of opinion.... – APC Nov 14 '11 at 17:54
@APC, agreed. Updated the answer to add the disclaimer. – amit_g Nov 14 '11 at 18:01

score 2 · Answer 2 · answered Nov 14 '11 at 17:42

If you have that list of tables in a text file, you may find it easier to load the text file into a temporary table and change your IN statement to reference the values using a subquery.

If you just wanted to build the IN string quickly, you could use regular expressions to add the delimiters.

Something like replace \n with ',\n'

Workaround inside IN OPERATOR

2 Answers2