Function SUBSTRING_INDEX on MySql database

[Antonio1971]

Hi all.

On table MySql database I have this string memorized for each row :

A200 - Al, A202 - John, A505 - Jack, A503 - Marion, A502 - Marie

The value of A200 is the UserNumber and the value of Al is the UserName.

For each row I can have a minimum of :

A200 - Al

And a maximum of n for UserNumber and UserName

Now I need this return :

+---------+
|  Output |
+---------+
|  Al     |
|  John   |
|  Jack   |
|  Marion |
|  Marie  |
+---------+

I have tried with :

mysql> SELECT
    SUBSTRING_INDEX(
        'A200 - Al, A202 - John, A505 - Jack, A503 - Marion, A502 - Marie',
        '-' ,- 1
    ) AS `Output`;

+--------+
| Output |
+--------+
|  Marie |
+--------+
1 row in set

How to resolve this ?

Please help me, thank you so much in advance.

Antonio.

Happy Christmas and Happy New Year.
Best wishes for the realization of all your desires.

[r937]

you’re going to need a numbers table

CREATE TABLE numbers (n INTEGER NOT NULL PRIMARY KEY);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6)...

now you can decode your string like this –

[code]SELECT SUBSTRING_INDEX(
SUBSTRING_INDEX(
‘A200 - Al, A202 - John, A505 - Jack, A503 - Marion, A502 - Marie’
, ', ’ , n )
, ’ - ', -1 ) AS Output
FROM numbers
WHERE n BETWEEN 1 AND
CHAR_LENGTH(‘A200 - Al, A202 - John, A505 - Jack, A503 - Marion, A502 - Marie’)

• CHAR_LENGTH(
  REPLACE(‘A200 - Al, A202 - John, A505 - Jack, A503 - Marion, A502 - Marie’,‘,’,‘’)
  )

• 1 [/code]

[Antonio1971]

Thank you so much for help.

mysql> DROP TABLE
IF EXISTS numbers;

CREATE TABLE numbers (n INTEGER NOT NULL PRIMARY KEY);

INSERT INTO numbers
VALUES
	(0),
	(1),
	(2),
	(3),
	(4),
	(5),
	(6);

SELECT
	SUBSTRING_INDEX(
		SUBSTRING_INDEX(
			'A200 - Al, A202 - John, A505 - Jack, A503 - Marion, A502 - Marie',
			', ',
			n
		),
		' - ',
		- 1
	) AS `Output`
FROM
	numbers
WHERE
	n BETWEEN 1
AND CHAR_LENGTH(
	'A200 - Al, A202 - John, A505 - Jack, A503 - Marion, A502 - Marie'
) - CHAR_LENGTH(
	REPLACE (
		'A200 - Al, A202 - John, A505 - Jack, A503 - Marion, A502 - Marie',
		',',
		''
	)
) + 1;
Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 7 rows affected
Records: 7  Duplicates: 0  Warnings: 0

+--------+
| Output |
+--------+
| Al     |
| John   |
| Jack   |
| Marion |
| Marie  |
+--------+
5 rows in set