Search and replace in strings

from the Artful MySQL Tips List

A table of digits ...

CREATE TABLE digits(digit int);
INSERT INTO digits VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

has many uses. With it, you can easily generate integer sequences of any length, eg 0 through 29 ...

SELECT CONCAT(tens.digit, ones.digit)+0 AS number 
FROM digits tens
JOIN digits ones ON tens.digit <=2 
ORDER BY number;

Likewise for a million ordered rows:

create table nums(i int);
insert into nums
SELECT CONCAT(d5.digit,d4.digit,d3.digit,d2.digit,d1.digit,d0.digit)+0 AS number 
FROM digits d5      
JOIN digits d4
JOIN digits d3
JOIN digits d2
JOIN digits d1
JOIN digits d0 
ORDER BY number; 

MySQL REGEXP returns only booleans; so it cannot do string search-and-replace. A digits table can help accomplish what REGEXP cannot, for example to remove all non-numeric characters from a set of string values up to 256 characters long, use a join from the table containing the target strings to the digits table such that the join walks the strings character by character, eliminating non-digits:

  SELECT 'V-1.234.567' as str
  SELECT 'X-ABC-123.9' as str
) AS foo
  SELECT CONCAT(tens.digit, ones.digit)+0 AS num 
  FROM digits tens
  JOIN digits ones 
  HAVING num < 256
  ORDER BY num
) AS ints ON num BETWEEN 1 AND LENGTH(str)
WHERE SUBSTRING(str,num,1) BETWEEN '0' AND '9'                  
| str         | Nums    |
| V-1.234.567 | 1234567 |
| X-ABC-123.9 | 1239    |

(based on a forum posting by Felix Geerinckx)

Return to the Artful MySQL Tips page