How can I set a numeric wildcard in MySQL?

Odi
July 24, 2024
137 views
3 votes
3 Answers

I’m trying to create a SQL script that finds codes in two different databases. I want to set a parameter that has a numeric wildcard.

Codes are often written like this for example, ABC108, ABC109, DEF47213, etc. I set the parameter as ABC[0-9]+ to return all possible variations of codes that start with ABC, but that resulted in a blank output.

I tried the following, expecting the most recent code made in both databases to be outputted:

SET @investment_code = 'ABC[0-9]+';

SELECT 
    investment_name, 
    i.service_client_id, 
    currency_symbol, 
    investment_code C_investment_codes
FROM client.investments i
LEFT JOIN client.currencies c
    ON i.currency_id = c.id
WHERE i.investment_code COLLATE utf8mb4_unicode_ci LIKE @investment_code
ORDER BY investment_code DESC
LIMIT 1;

SELECT 
`key`, 
service_client_id, 
`value` SD_investment_codes
FROM client_data.standing_data sd
WHERE sd.`value` COLLATE utf8mb4_unicode_ci LIKE @investment_code
ORDER BY `value` DESC
LIMIT 1;

This resulted in a blank output. When I use SET @investment_code = 'ABC%'; I get results, but I specifically want to set the parameter for digits to follow ABC. This is my expected output:

C_investment_codes	SD_investment_codes
ABC109	ABC109

Answers

- Peabodyjr
- July 24, 2024 at 8:45 pm
- 0 votes
0
In MySQL, the LIKE operator does not support regular expressions directly. Instead, you can use the % wildcard to match any sequence of characters.

For your script, you can leverage the REGEXP operator available in MySQL, which allows you to use regular expressions. Here’s an example
```
SET @investment_code = '^ABC[0-9]+$';

SELECT 
    investment_name, 
    i.service_client_id, 
    currency_symbol, 
    investment_code AS C_investment_codes
FROM client.investments i
LEFT JOIN client.currencies c
    ON i.currency_id = c.id
WHERE i.investment_code REGEXP @investment_code
ORDER BY investment_code DESC
LIMIT 1;

table
SELECT 
    `key`, 
    service_client_id, 
    `value` AS SD_investment_codes
FROM client_data.standing_data sd
WHERE sd.`value` REGEXP @investment_code
ORDER BY `value` DESC
LIMIT 1;
```
Login or Signup to reply.

- AmmarAmeerdeen
- July 24, 2024 at 8:46 pm
- 0 votes
0
To achieve the desired result of finding codes that start with "ABC" followed by digits, you can use SQL pattern matching with the LIKE operator combined with the REGEXP operator for regular expressions. In MySQL, the LIKE operator doesn’t support full regular expressions, but REGEXP does.

Here’s how you can adjust your script to use both LIKE and REGEXP:
```
SET @investment_code_like = 'ABC%';
SET @investment_code_regex = '^ABC[0-9]+';

-- Query from the first database
SELECT 
    investment_name, 
    i.service_client_id, 
    currency_symbol, 
    investment_code AS C_investment_codes
FROM client.investments i
LEFT JOIN client.currencies c
    ON i.currency_id = c.id
WHERE i.investment_code COLLATE utf8mb4_unicode_ci LIKE @investment_code_like
  AND i.investment_code REGEXP @investment_code_regex
ORDER BY investment_code DESC
LIMIT 1;

-- Query from the second database
SELECT 
    `key`, 
    service_client_id, 
    `value` AS SD_investment_codes
FROM client_data.standing_data sd
WHERE sd.`value` COLLATE utf8mb4_unicode_ci LIKE @investment_code_like
  AND sd.`value` REGEXP @investment_code_regex
ORDER BY `value` DESC
LIMIT 1;
```
Explanation:
1. SET @investment_code_like = ‘ABC%’; – This sets the parameter to match any string that starts with "ABC".
2. SET @investment_code_regex = ‘^ABC[0-9]+’; – This sets the regular expression to match strings that start with "ABC" followed by one or more digits.
  In the
3. In the WHERE clause, both conditions are used:
- LIKE @investment_code_like to use the pattern matching for strings that start with "ABC".
- REGEXP @investment_code_regex to ensure that the string actually follows the "ABC" with digits pattern.
1. ORDER BY investment_code DESC LIMIT 1; ensures that the most recent (highest value) code is selected.
This way, you ensure that the query matches codes that start with "ABC" followed by digits and not just any characters after "ABC".
Login or Signup to reply.