有没有办法从 MySQL 的列中提取与正则表达式匹配的文本? [英] Is there a way to extract text matching a regular expression from a column in MySQL?
问题描述
例如,如果特定列中的所有条目都具有 [az]+[0-9]+ 形式,那么如何仅提取前导字母,以便 asdf123 和 as3456 返回 'asdf' 和 'as',分别?
For example, if all entries in a particular column have the form [a-z]+[0-9]+, how can one extract just the leading letters, so that asdf123 and as3456 return 'asdf' and 'as', respectively?
推荐答案
这在 mysql 正则表达式功能中是不可能的(除非安装一些似乎是为此设计的 udf).从技术上讲,由于 [0-9] 是一个有限集合,您可以在 LEAST
中抛出 10 个 LOCATE
(注意 & invaliddate 0),然后使用 SUBSTRING
.不是一张漂亮的照片:
This is not posible with mysql regex capablities (unless installing some udf's which appear to be designed for that). Technically, as [0-9] is a limited set you could throw 10 LOCATE
's in LEAST
(watch out for & invalitdate 0), and use SUBSTRING
. Not a pretty picture:
SET @var ='asjdasd35433';
SELECT SUBSTRING(@var,1,
LEAST(
IF(LOCATE(0,@var)=0,LENGTH(@var),LOCATE(0,@var)),
IF(LOCATE(1,@var)=0,LENGTH(@var),LOCATE(1,@var)),
IF(LOCATE(2,@var)=0,LENGTH(@var),LOCATE(2,@var)),
IF(LOCATE(3,@var)=0,LENGTH(@var),LOCATE(3,@var)),
IF(LOCATE(4,@var)=0,LENGTH(@var),LOCATE(4,@var)),
IF(LOCATE(5,@var)=0,LENGTH(@var),LOCATE(5,@var)),
IF(LOCATE(6,@var)=0,LENGTH(@var),LOCATE(6,@var)),
IF(LOCATE(7,@var)=0,LENGTH(@var),LOCATE(7,@var)),
IF(LOCATE(8,@var)=0,LENGTH(@var),LOCATE(8,@var)),
IF(LOCATE(9,@var)=0,LENGTH(@var),LOCATE(9,@var))
) -1 ) as 'result';
+---------+
| result |
+---------+
| asjdasd |
+---------+
如果您需要,请查看 udf.否则,您最好获取该字段并在 MySQL 之外对其进行操作.
If you need it look at udf's. Otherwise you're just better of fetching the field and manipulate it outside MySQL.
如果数字适合整数,脏黑客可能会导致:
SET @var ='asjdasd35433';
SELECT SUBSTRING(@var,1,LENGTH(@var)-LENGTH(CAST(REVERSE(@var) as UNSIGNED)));
+---------+
| result |
+---------+
| asjdasd |
+---------+
这篇关于有没有办法从 MySQL 的列中提取与正则表达式匹配的文本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!