CODEWITHSUNDEEP
mysqlregex
osumatu
osumatu

Reputation: 442

MySQL Get the integer value between two strings

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!

Upvotes: 4

Views: 218

Answers (2)

Tudor Constantin
Tudor Constantin

Reputation: 26861

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;

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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

Upvotes: 2

Related Questions