prat prat - 5 months ago 17
SQL Question

mysql query to Seperate string with unknown length and pattern is as shown in example,i need to seperate 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

Answer

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 inot 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 .. :)