I recently needed to convert a simple list of strings into something that could be used in a SQL IN clause. The format for the IN clause that I needed was quoted strings with commas between each value.
In other words I needed to convert a plain list this:
Alice Bob Carol Dan Erin Frank Eve
Into a list with a quote at the start and end of each line and a comma between items. Something like this:
'Alice', 'Bob', 'Carol', 'Dan', 'Erin', 'Frank', 'Eve'
Whether the result had carriage returns wasn’t important but the quotes had to be in place and the commas all had to be correct. For a little list like this it’s quickest to do it by hand but the list I had was about 150 items long – I wasn’t about to do that by hand.
To do this fire up everyone’s favourite text editor – Notepad++. Open the file containing the list to be quoted then:
- Press Ctrl-H to bring up the Replace dialog.
- Enter (.+) in the Find what field.
- Enter ‘\1’, in the Replace with field.
- Select Regular expression at the bottom of the dialog.
- Press Replace All
What this does is use the regular expression (.+) to select each non-empty line (since the “. matches newline” box isn’t checked the regular expression works per-line). The round brackets around the regular expression denote a capture group which is referenced in the “Replace with” expression as \1. The “Replace with” expression simply wraps what ever was captured in the “Find what” expression in single quotes and places a comma at the end. The final result has an extra comma at the end of the list but that is easy to delete by hand.
Removing the Line Breaks
SQL isn’t sensitive to the line breaks but what if you want to get rid of them anyway. This, it turns out, is harder than it should be. It would be expected that using extended mode and \n should find all the new line characters but in my testing this almost never seems to work. The most reliable method I’ve found to replace new lines is to place the cursor at the end of the first line and then press “Shift + Right Arrow” to advance the cursor to the next line. An invisible new line character is selected. If you press Ctrl-H how this invisible character is placed in the “Find what” field. Putting a space in the “Replace with” field will neatly format the multiple lines into a single line.