Building Large SQL In List – Using RegEx and NotePad++

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

where firstname in (

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


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 - After the Replace
Results – After the Replace


where firstname in (

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).


Leave a Reply

Recent Posts




All Rights Reserved Theme by 404 THEME.