我有一个类似如下的字符串:pi_18944000_780345308_54210001000_345900_text
如何提取位于其中的所有Ids并进行存储?
我尝试了这样的东西:
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;
这是可行的,但它真的很复杂。只是想看看有没有更好的方法?
发布于 2019-04-16 03:56:29
这里有一个稍微简单一些的解决方案。它使用SUBSTRING_INDEX()
内置函数。它只需要引用字符串表达式一次。
mysql> set @str = 'pi_18944000_780345308_54210001000_345900_text';
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str, '_', 3), '_', -1) AS n;
+-----------+
| n |
+-----------+
| 780345308 |
+-----------+
请参阅https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index
下一次,如果需要在SQL表达式中引用单个id,则应该考虑不在一个字符串中存储多个id。
https://stackoverflow.com/questions/55695974
复制相似问题