Swapping Numbers in VS Code Using Regex - A Solution for SQL Undo Scripts

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 + Click or Ctrl + D is 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:

1. (\b\d+(\.\d+)?\b) → Captures the first number (Group 1)
   • \b ensures it's a complete number.
   • \d+ matches the integer part.
   • (\.\d+)? allows an optional decimal part.
2. (.*?) → Captures whatever is between the numbers (Group 3)
   • .*? matches any characters but stops at the next number.
3. (\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:

1. Open Find & Replace (Ctrl + H / Cmd + H).
2. Enable Regex Mode (.*).
3. Enter the regex and replacement pattern.
4. Click Replace All.

This method ensures accuracy and efficiency in handling numerical swaps in SQL, logs, and other structured data files. 🚀