I have a string that looks like this:
pi_18944000_780345308_54210001000_345900_text
How do I extract all the Ids located in it and store it?
I tried something like this:
set @a = (SELECT
SUBSTRING(SUBSTRING(SUBSTRING('pi_18944000_780345308_54210001000_345900_text',
(LOCATE('_', 'pi_18944000_780345308_54210001000_345900_text'))), 2), 1,
LOCATE('_', SUBSTRING(SUBSTRING('pi_18944000_780345308_54210001000_345900_text',
(LOCATE('_', 'pi_18944000_780345308_54210001000_345900_text'))), 2)) - 1)
);
select @a;
set @b = (SELECT
SUBSTRING(SUBSTRING(SUBSTRING(SUBSTRING(SUBSTRING(
'pi_18944000_780345308_54210001000_345900_text' , ( LOCATE('_', 'pi_18944000_780345308_54210001000_345900_text' )) ), 2),
LOCATE('_', SUBSTRING(SUBSTRING( 'pi_18944000_780345308_54210001000_345900_text', ( LOCATE('_', 'pi_18944000_780345308_54210001000_345900_text'
))), 2))), 2), 1, LOCATE('_', SUBSTRING(SUBSTRING(SUBSTRING(SUBSTRING( 'pi_18944000_780345308_54210001000_345900_text' , (
LOCATE( '_', 'pi_18944000_780345308_54210001000_345900_text' ))), 2),
LOCATE('_', SUBSTRING(SUBSTRING( 'pi_18944000_780345308_54210001000_345900_text', (
LOCATE('_', 'pi_18944000_780345308_54210001000_345900_text' ))) , 2))), 2)) - 1) );
select @b;
This works but its really complex. Just wanted to see if there is a better way to do this?
Here's a somewhat simpler solution. It uses the SUBSTRING_INDEX()
built-in function. It only requires referencing the string expression once.
mysql> set @str = 'pi_18944000_780345308_54210001000_345900_text';
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str, '_', 3), '_', -1) AS n;
+-----------+
| n |
+-----------+
| 780345308 |
+-----------+
See https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index
Next time you should consider not storing multiple id's in a string if you need to reference the individual id's in an SQL expression.