Recently I had a side project where I repeatedly needed to manually run an SQL query based on some data received from a third party. They would send me the data containing a list of secure codes, and I would have to include those codes in a query to find matches in my database, like so:
The problem was - the third party would send us codes as a single code per line in a text file, as so:
AABBCCDDDBBCCDDEEFA0B2C3D459G8H7I6K5etc…No delimiters or commas etc. And there were sometimes hundreds of lines per file that I had to manually run down and place an ‘ (single quote) at the start, and a ‘. (single quote and comma) at the end. Line. By. Line.
This was getting tiresome to do several times a day, each time a fresh file was sent to me. There must be an easier way. Turns out there was - Regular Expression Searching!
Now what I can do is open the file in my favourite editor TextMate. Then I can go to Edit -> Find. Ensure that the ‘Regular Expression’ checkbox is ticked, then enter in ^ as the ‘Find’ string (RegExp for ‘Start of Line’), and a ‘ (single quote) as the ‘Replace’ string.
Then do another Find, and use $ as the ‘Find’ string (RegExp for ‘End of Line’), and a ‘, (single quote and comma) as the ‘Replace’ string.
Voila! Done. The codes will now all be delimited. All that you need to do is to delete the trailing comma from the very last code, and then you can cut and paste the contents of the text file into the IN clause of the SQL query and run it instantly!