Thursday, January 24, 2013

How to add single quote at beginning and end of word

For you who do query a lot to a database, from: MSSQL, Oracle, including, you may need to think is there anyway to add single quote at beginning and end of words and add comma after end of word. It is very time consuming if you need to add it manually if you want to query for 500 Id.

You can do this much faster using tool, such as TextPad, download from and install it.

Let say you need to query for Id from an Excel file. Copy and paste the Ids into TextPad, example:

Go to Search menu and select Replace... or F8 for shortcut
in Find What: .+
in Replace With: '\0',
in Conditions: select Text and Regular expression 
and click Replace All.

And the result:

You may need to remove comma in last line. So you can put this Ids into your query filter, example:
SELECT Id, Name, Email FROM Contact Where Id IN ('0038000000zYkACAA0',

If you are using Notepad++ since it is free, change to this:
in Find What: .+
in Replace With: '$0',
in Search Mode: select Regular expression