In MySQL how to get numbers after a specific word

Question

I have a table with descriptions of products. Within those descriptions are product dimensions, though not all in the same position.

How can I pull out just the widths in numbers from the following example data Description

Table Width (cm) 100
Bed, Width: 90cm
Office Desk Width - 200 cm. Color - Black
Couch with Width of 500 cm
Chair - width(50.5cm)

Have used substring_index function to get the text between 'width' and 'cm', but haven't been able to grab then just the numbers within that. (and that doesn't work for record 1 above

SELECT description, substring_index(substring_index(description, 'width', -1),'cm', 1)
FROM productlisting

Expected output:

100
90
200
500
50.5

Actual

: 90
- 200
of 500
(50.5

With no consistency of input, this will be a struggle. You will probably need to parse the data several times to deal with every situation — Strawberry, Aug 19 '19 at 12:05
This might help. https://stackoverflow.com/questions/37268248/how-to-get-only-digits-from-string-in-mysql/37269038#37269038 — little_coder, Aug 19 '19 at 12:06
Is it too late to redesign the database to be more user friendly? — RiggsFolly, Aug 19 '19 at 12:10
use regex_replace() just like select regex_replace(description,'[^0-9]','') — Primit, Aug 19 '19 at 12:54

score 2 · Accepted Answer · answered Aug 19 '19 at 12:26

2

In MySQL 8+, you can use regexp_replace() to get the first number after "Width"

select regexp_replace(d, '.*Width[^0-9]*', '') + 0

answered Aug 19 '19 at 12:26

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon, I should have mentioned however we are still on MySQL 5.7. So I'm out of luck with this one. Another reason here to upgrade but theres some hesitation from those responsible for the db. – Mark McP Aug 19 '19 at 12:59
Hi again Gordon. Managed to dump the table into an 8.0 DB. I'm using MySQL Workbench to run the query, however unless my limit is set very low (<100 rows) it returns : Error Code: 3699. Timeout exceeded in regular expression match. The field I'm searching in is a longtext field, with A full text index. The table has 190K rows in total – Mark McP Aug 19 '19 at 13:57
@MarkMcP . . . I don't really understand your comment. – Gordon Linoff Aug 19 '19 at 13:59
In MySQL workbench I run: select regexp_replace(description, '.*Width[^0-9]*', '') from productlist + 0 The system then throws up message: Error Code: 3699. Timeout exceeded in regular expression match. 0.204 sec – Mark McP Aug 19 '19 at 21:38

score 1 · Answer 2 · answered Aug 19 '19 at 13:34

If there is only 1 number in the description, just like your sample data, you can use Mysql string functions to get that number like this:

select
  p.description,
  substr(p.description, min(locate(d.digit, p.description))) + 0 number
from (
  select 0 digit union all select 1 union all
  select 2 union all select 3 union all
  select 4 union all select 5 union all
  select 6 union all select 7 union all
  select 8 union all select 9 
) d inner join productlisting p
on p.description like concat('%', d.digit, '%')
group by p.description

See the demo.
Results:

|description                                | number |
| ----------------------------------------- | ------ |
| Bed, Width: 90cm                          | 90     |
| Chair - width(50.5cm)                     | 50.5   |
| Couch with Width of 500 cm                | 500    |
| Office Desk Width - 200 cm. Color - Black | 200    |
| Table Width (cm) 100                      | 100    |

Thanks forpas unfortunately though my sample data was simplified. Actual data can have more than 1 number, eg: - "Width 680mm Height 920mm Weight 23Kg Specifications for the 5Ft Option Length 1460mm " — Mark McP, Aug 19 '19 at 21:59
Better change your design. Sql code cannot spot a number anywhere in the description among other numbers when there is no pattern to search for. — forpas, Aug 20 '19 at 10:19
Wish it was that simple. The data is coming from other sources and all over the place. We will look into more advanced solutions. cheers — Mark McP, Dec 12 '19 at 14:59

In MySQL how to get numbers after a specific word

2 Answers2