MySQL - extract number from data field using SUBSTRING

Question

I'd like to extract the number between NUMBER and ;. So far I can extract the data up to the number, but I don't want anything after the number. e.g.,

SELECT
    SUBSTRING(field, LOCATE('NUMBER=', rrule) + 7)
FROM table

Data field:

DATA:PASS=X12;NUMBER=331;FIELD=1
DATA:PASS=X12;NUMBER=2;FOO=BAR;FIELD=1

Desired Output:

331
2

fthiella · Accepted Answer · 2014-11-24 20:28:09Z

3

You can use a combination of SUBSTRING_INDEX functions:

SELECT
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(field, 'NUMBER=', -1),
    ';',
  1)
FROM
  tablename

Please see an example fiddle here.

The inner SUBSTRING_INDEX will return everything after the NUMBER= string, while the second will return everything before the ; returned by the inner function.

answered Nov 24, 2014 at 20:28

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - extract number from data field using SUBSTRING

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related