Suppose you have a large list of items that you want to put into a SQL in list statement, like this:


where firstname in (
'Aaron',
'Abbey',
'Abe'
)

But the data you have in is Excel or a text file, and doesn’t have the quotes and commas around it.

You can quickly modify the data using the RegEx Replace All feature of NotePad++.

Data to change and the Replace Commands

 

NotepadPlusPlus_RegEx_Replace_For_SQL_In_List

Data to be changed, and then do CNTL-H or Search/Replace… from the top menu.

 

This is what it means.  The “Find What” is set to (.*).  That means find all characters on each line, and capture it. The parentheses are the capture symbols, everything between them is captured.  The Dot means any-character, and the * means 0 or more of those characters.

The “Replace with” is set to ‘\1’.  The backslash-one refers to the first capture in parentheses from the “Find what” box above.  Then the other symbols happen to be what I wanted to add, a single-quote at the beginning, and a single-quote at the end.

 

Results

Results - After the Replace

Results – After the Replace

 

where firstname in (
'Aaron',
'Abbey',
'Abe',
)

The only issue is the last name in the list will also have a comma after it.  You will just have to change that manually.  (I.e. remove the comma after “Abe” above).

 

Tagged with:

Filed under: SQL