MySQL Get the integer value between two strings

Question

I am trying to get the integer value between two specific strings but I am stacked a little bit.

Example full string:

"The real ABC4_ string is probably company secret."

I need to get the "4" between "ABC" and "_". First I've came up with following script:

select substring_index(substring_index('The real ABC4_ string is probably company secret.', 'ABC', -1),'_', 1);

It gives me 4, perfect! But the problem is if ABC occurs more than one time in the string it fails. I can't simply increase the counter also since I don't know how many times it will be in the future. I have to get first occurrence of that regex: ABC[DIGIT]_

I've seen REGEXP_SUBSTR function but since we use older version of MySQL than 8.0 I can't use it also.

Any suggestions?

Thanks!

@RickJames Yes I had to move this to code, it's probably the most efficient (actually the only one I came up with) solution. — osumatu
– osumatu, Commented Oct 15, 2019 at 7:25

Madhur Bhaiya · Accepted Answer · 2019-10-09 11:41:35Z

2

Without using Regex, here is an approach using LOCATE(), and other string functions:

SET @input_string = 'The real ABC4_ string is probably company secret.';

SELECT TRIM(LEADING 'ABC' 
              FROM SUBSTRING_INDEX(
                     SUBSTR(@input_string FROM 
                       LOCATE('ABC', @input_string)
                           )
                     ,'_', 1
                     )
           ) AS number_extracted;

| number_extracted |
| ---------------- |
| 4                |

View on DB Fiddle

answered Oct 9, 2019 at 11:41

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

2 Comments

osumatu Over a year ago

For a string like this it fails: SET @input_string = 'The real ABC_ string is probably company secretABC4_.';

osumatu Over a year ago

Except duplicate cases like mine (in the previous comment) it works perfectly, I am approving the answer but please keep in mind this scenario dear future readers.

Rick James · Accepted Answer · 2019-10-15 18:26:29Z

1

Another way of (ab)using the LOCATE() function:

select    substr('The real ABC4_ string is probably company secret.',
   locate('ABC', 'The real ABC4_ string is probably company secret.') + 3,
      locate('_','The real ABC4_ string is probably company secret.') - 
   locate('ABC', 'The real ABC4_ string is probably company secret.') - 3) AS num;

edited Oct 15, 2019 at 18:26

Rick James

144k15 gold badges144 silver badges255 bronze badges

answered Oct 9, 2019 at 11:47

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

1 Comment

osumatu Over a year ago

For a string like this it fails: SET @input_string = 'The real ABC_ string is probably company secretABC4_.'; As I understand from the query, you are looking for the first ABC string than the first _ string and get between those two. I need to look for all the scenarios like 1- ABC exists, 2- _ exists, 3- integer between these two exists 4- If there is more than one satisfying below rules, take the first one.

Collectives™ on Stack Overflow

MySQL Get the integer value between two strings

2 Answers 2

View on DB Fiddle

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related