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
str,
GROUP_CONCAT(SUBSTRING(str, num, 1) ORDER BY num SEPARATOR '') AS Nums
FROM (
SELECT 'V-1.234.567' as str
UNION
SELECT 'X-ABC-123.9' as str
) AS foo
JOIN (
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'
GROUP BY str;
+-------------+---------+
| str | Nums |
+-------------+---------+
| V-1.234.567 | 1234567 |
| X-ABC-123.9 | 1239 |
+-------------+---------+
(based on a forum posting by Felix Geerinckx) |
|