mysql查询解析未知长度的字符串 [英] mysql query to parse string with unknown length

问题描述

示例模式如示例所示，我想在前后分开值:(冒号)

示例字符串模式:23:4,13:6^12:1^15:3^45:6,99:2,64:1^..^>

注意:大写数字之间的逗号可能会有所不同例如

 23:4,13:6^ - 1逗号^12:1^ - 无逗号^45:6,99:2,64:1^ - 2 个逗号

预期输出:

 ColumnA ColumnB------------------23 413 612 115 345 6

解决方案

谢谢@Barmar

我找到了解决方案

/* 用空格替换特殊字符并在变量中取值*/

 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);

/* 会话变量 */

 SET @VRow := 0;

/* 将子串索引分隔成列 */

 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 

感谢 Stackoverflow .. :)

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

解决方案

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查询解析未知长度的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！