Swapping Numbers in VS Code Using Regex - A Solution for SQL Undo ScriptsChris Child | 2025-04-02 | 3 min read| Comments
Problem Statement
When writing undo scripts in SQL, it’s common to reference existing data as part of the WHERE clause. If the script is generated dynamically, you may need to swap values in statements to reverse operations. However, doing this manually can be time-consuming and error-prone.
Example: Undo Script Scenario
Imagine you have an SQL script that updates values:
UPDATE Orders SET Price = 49.99 WHERE OrderID = 102 AND PRICE = 89;
UPDATE Orders SET Price = 79 WHERE OrderID = 203 AND PRICE = 45.5;
UPDATE Orders SET Price = 22.50 WHERE OrderID = 305 AND PRICE = 30;
To undo these updates, you need to swap the price values while keeping the OrderID unchanged:
UPDATE Orders SET Price = 89 WHERE OrderID = 102 AND PRICE = 49.99;
UPDATE Orders SET Price = 45.5 WHERE OrderID = 203 AND PRICE = 79;
UPDATE Orders SET Price = 30 WHERE OrderID = 305 AND PRICE = 22.50;
This requires swapping two numbers per line, and doing it manually is tedious.
Issue with Multi-Line Select in VS Code
VS Code’s multi-cursor editing is powerful, but when working with numbers (especially decimals), it has a limitation:
- Selecting numbers using
Alt + ClickorCtrl + Dis easy for integers but tricky for decimals, as the cursor may not select the whole number correctly. - Manually selecting each number and swapping them is inefficient.
Thus, we need a regex-based find & replace solution to automate the process.
The Regex Solution
We use Find & Replace with Regular Expressions in VS Code to swap two numbers on the same line while keeping the separator unchanged.
Regex Pattern (Find):
(\b\d+(\.\d+)?\b)(.*?)(\b\d+(\.\d+)?\b)
Replacement Pattern:
$4$3$1
Explanation of the Regex Capture Groups:
(\b\d+(\.\d+)?\b)→ Captures the first number (Group 1)\bensures it's a complete number.\d+matches the integer part.(\.\d+)?allows an optional decimal part.
(.*?)→ Captures whatever is between the numbers (Group 3).*?matches any characters but stops at the next number.
(\b\d+(\.\d+)?\b)→ Captures the second number (Group 4)- Same rules as Group 1, ensuring it matches a full number.
How the Replacement Works:
$4$3$1
$4→ Puts the second number first.$3→ Retains the separator (e.g., spaces, dashes, commas).$1→ Moves the first number to the second position.
Example Transformation:
Before Replacement:
UPDATE Orders SET Price = 49.99 WHERE OrderID = 102 AND PRICE = 89;
UPDATE Orders SET Price = 79 WHERE OrderID = 203 AND PRICE = 45.5;
UPDATE Orders SET Price = 22.50 WHERE OrderID = 305 AND PRICE = 30;
After Replacement:
UPDATE Orders SET Price = 89 WHERE OrderID = 102 AND PRICE = 49.99;
UPDATE Orders SET Price = 45.5 WHERE OrderID = 203 AND PRICE = 79;
UPDATE Orders SET Price = 30 WHERE OrderID = 305 AND PRICE = 22.50;
Conclusion
This regex-based approach automates the swapping of numbers, making SQL undo scripts (or any similar tasks) much easier. Instead of struggling with multi-cursor selection, simply:
- Open Find & Replace (
Ctrl + H/Cmd + H). - Enable Regex Mode
(.*). - Enter the regex and replacement pattern.
- Click Replace All.
This method ensures accuracy and efficiency in handling numerical swaps in SQL, logs, and other structured data files. 🚀