mysql query to parse string with unknown length

Question

Sample pattern is as shown in example, i want to separate values before and after : (colon)

Sample string pattern: 23:4,13:6^12:1^15:3^45:6,99:2,64:1^.....^

Note: between caps number commas may vary for e.g

 23:4,13:6^  - 1comma
 ^12:1^   - no comma
 ^45:6,99:2,64:1^  - 2 commas

Expected output:

 ColumnA    ColumnB
 ------------------
    23        4
    13        6
    12        1
    15        3
    45        6

Take a look at the `SUBSTRING_INDEX` function. It can be used to find the parts of a string before or after a certain number of delimiters. — Barmar, Jul 01 '16 at 05:06
i tried with substring index but not working for unknown length string — prat, Jul 01 '16 at 05:10
Why you want do it with MySQL query? It's match easier do it with other language.. Which one are you using? — Sergey Khalitov, Jul 01 '16 at 05:13
@prat ok, there's only one solution - stored procedure. It's not posible to do what you want with one query. — Sergey Khalitov, Jul 01 '16 at 05:26

score 1 · Answer 1 · answered Jul 01 '16 at 05:08

1

You can simply replace the delimiter characters with spaces.

SELECT REPLACE(REPLACE(REPLACE(colname, ',', ' '), ':', ' '), '^', ' ')
FROM yourTable

answered Jul 01 '16 at 05:08

Barmar

741,623
53
500
612

trying with this space thing , but after that how to manage that in diffrent columns because unknown length string ? – prat Jul 01 '16 at 05:27
That's a very different problem. It's not really possible to make dynamic columns in SQL, you need to use a stored procedure that creates dynamic SQL. – Barmar Jul 01 '16 at 05:39
As someone said in the comments, this would be easier in a real programming language that processes the query results. Then you can split it into an array and process that. – Barmar Jul 01 '16 at 05:40
See http://stackoverflow.com/questions/1096679/can-mysql-split-a-column – Barmar Jul 01 '16 at 05:52

prat · Accepted Answer · 2020-10-01T10:31:37.603

Thanks @Barmar

I found solution

/* Replaced Special characters with space and took value in Varibale*/

  SET @VInput = (SELECT REPLACE(REPLACE(REPLACE("23:4,13:6^12:1^15:3^45:6,99:2,64:1^" , ',', ' '), ':', ' '), '^', ' ') FROM TABLE_NAME WHERE CONDITION);

/* Session varible */

  SET @VRow := 0;

/* Substring Index to seperate out into coloumn */

  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@VInput, ' ', (@VRow := @VRow + 1) * 2 - 1), ' ', -1) as RESP, SUBSTRING_INDEX(SUBSTRING_INDEX(@VInput, ' ', (@VRow) * 2), ' ', -1)  as Count FROM INFORMATION_SCHEMA.`COLUMNS` WHERE @VRow * 2 < CHAR_LENGTH(@VInput) - CHAR_LENGTH(REPLACE(@VInput, ' ', ''));

Thanks Stackoverflow .. :)

mysql query to parse string with unknown length

2 Answers2

Linked