How to use Notepad + + recording macro function to quickly add single quotation marks to SQL search criteria

Time:2020-10-24

When we have many pen conditions to search the database, we often use the following syntax


 SELECT
 *
 FROM
 Member
 WHERE
 accunt in
 (
 'Tom',
 'John',
 'May'
 )

In the above syntax in search conditions, there are three account conditions: Tom, John and may. If the amount of data is small, you can add single quotation marks before and after the account number and a comma after the account number. However, when you need to query 50 account numbers at a time, it will take a lot of time to input.
I used to use the concatenate function in Excel to process such data, but one day my colleagues taught me to use the macro function of Notepad + + to complete this task

I think it’s faster than Excel to share it here

1. Paste the search conditions to be processed to Notepad + +. As shown in the following figure, there are 25 strokes (the English names in the figure are the list randomly searched from Google)

2. Because macro is to record your operation, first move the cursor to the starting position of the first data, and then press the macro button on the Notepad + + toolbar to start recording

3. Since the cursor is already at the beginning, that is, the position of the first data, please use the keyboard directly and input according to the following keyboard key sequence (all the keys in the brackets below are keyboard keys)

「’」→「End」→「’」→「,」→「↓」→「Home」

4. After completing the third step, it should look like the following figure. The first search condition has been added with single quotation marks and the last comma

5. After confirming step 4, click the button below to stop recording macro

6. After recording, the remaining 24 records can be processed by macro. Run once to see if it is normal

Click the button below to play the macro (the cursor is still at the beginning of the second data)

7. After playing the macro, you can see that the second data has been processed and the cursor has moved to the starting position of the third data

8. After verifying that the macro is OK, let the macro run to the last data in one breath

Click the button in step 1 below to repeat macro execution. Then in the prompt window, select execute to the end of file, and then press execute

9. After the execution is completed, you can see that all the conditions are enclosed with single quotation marks and commas. Only note that there will be a comma at the end of the last search condition

Remember to delete the comma and paste it into the SQL syntax, otherwise the query will fail~

(after the execution is completed, the window that repeatedly executes a single macro will not be closed automatically. Press cancel at this time.)

10. After completing the above steps, the requirements have been met. Only if Notepad + + is closed at this time, the macro will be recorded again next time the same situation occurs

Fortunately, Notepad + + has the function of storing macro, so we can save the macro recorded this time and use it directly next time

Click the button in step 1 below to open the macro storage window. In the input field of “name”, give a name for this macro. After that, press the “OK” button

11. At this time, close Notepad + + and open it again. Paste the unprocessed data again. Click the “repeat single macro” button to select the macro we just saved in the window

Select the macro you just saved, then select execute to the end of the file and press execute. You can see the result after processing as in step 9~

Recommended Today

Comparison and analysis of Py = > redis and python operation redis syntax

preface R: For redis cli P: Redis for Python get ready pip install redis pool = redis.ConnectionPool(host=’39.107.86.223′, port=6379, db=1) redis = redis.Redis(connection_pool=pool) Redis. All commands I have omitted all the following commands. If there are conflicts with Python built-in functions, I will add redis Global command Dbsize (number of returned keys) R: dbsize P: print(redis.dbsize()) […]