从不规则格式的字符串中间返回数字

问题描述

我在 MySQL 数据库中有一个字段，其中包含已购买产品的项目描述.其中一些是简单的英语描述，其他是部件号，还有一些仍然是部件号后跟描述.我已经使用 replace() 从字符串中删除了所有空格和破折号.

数据如下所示:

1938420985390asdfih1234812934810dflkasdasdfasldkjfaasdfjasdasd;flkjaklsdfadfsdf1234073927357sdapjfas1/4sdikhsd

我想回来:

19384209853901234812934810(空值)(空值)1234073927357(空值)

我真正需要的是编写一个 SQL，它会返回 13 位零件号，但不会返回额外的字母/字符.我也希望它返回实际数字，而不是匹配/不匹配的 1 或 0.

我尝试使用 REGEXP 函数(有人建议使用 regexp ('\d{13}') 或 regexp ('\p{13}') 但这些没有'行不通.[这些返回 0 或 1，而不是匹配的字符串部分.] 有什么建议吗?

谢谢！

解决方案

这是 MySQL 中的一项重要任务，没有用于返回正则表达式匹配的内置函数.但是因为您正在寻找恰好 13 位数字，所以您可以执行以下操作(显然将其扩展到您需要检查的位置数量...

-- 设置测试创建表 t (foo VARCHAR(30));插入 t 值('1938420985390asdfih'),('1234812934810dflkasd'),('asdfasldkjfaasdfjasd'),('asd;flkjaklsdf'),('adfsdf1234073927357sdapjfas'),('1/4sdikhsd')选择案例当 SUBSTR(foo,1,13) 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,1,13)当 SUBSTR(foo,2,13)​​ 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,2,13)当 SUBSTR(foo,3,13) 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,3,13)当 SUBSTR(foo,4,13) 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,4,13)当 SUBSTR(foo,5,13) 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,5,13)当 SUBSTR(foo,6,13) 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,6,13)当 SUBSTR(foo,7,13) 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,7,13)当 SUBSTR(foo,8,13) 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,8,13)当 SUBSTR(foo,9,13) 正则表达式 '^[0-9]{13}$' THEN SUBSTR(foo,9,13)END AS 数字从T-------------------19384209853901234812934810(空值)(空值)1234073927357(空值)

不，它不漂亮.但是您应该能够扩展它以有效地扫描"合理长度的字符串.

注意:正则表达式会检查整个 13 个字符的子字符串是否正好由 13 个字符组成，每个字符都是十进制数字(0 到 9).

I have a field in a MySQL database that contains item descriptions of purchased products. Some of these are descriptions in plain English, others are part numbers, and others still are part numbers followed by a description. I have removed all spaces and dashes from the strings with a replace().

1938420985390asdfih
1234812934810dflkasd
asdfasldkjfaasdfjasd
asd;flkjaklsdf
adfsdf1234073927357sdapjfas
1/4sdikhsd 

1938420985390
1234812934810
(null)
(null)
1234073927357
(null)

What I really need is to write a SQL that will return the 13 digit part numbers, but not the extra letters/characters. I would prefer that it return the actual number, too, rather than a 1 or 0 for match/ no match.

I tried using a REGEXP function (someone suggested regexp ('\d{13}') or regexp ('\p{13}') but these didn't work. [These returned a 0 or 1, and not the part of the string that matched.] Any suggestions?

Thanks!

解决方案

This is a non-trivial task in MySQL, there's no builtin function for returning a regular expression match. But because you are looking for exactly 13 digits, you could do something like this (obviously extend this to the number of positions you need to check...

-- setup test
CREATE TABLE t (foo VARCHAR(30));
INSERT INTO t VALUES 
('1938420985390asdfih')
,('1234812934810dflkasd')
,('asdfasldkjfaasdfjasd')
,('asd;flkjaklsdf')
,('adfsdf1234073927357sdapjfas')
,('1/4sdikhsd')


SELECT CASE
       WHEN SUBSTR(foo,1,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,1,13)
       WHEN SUBSTR(foo,2,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,2,13)
       WHEN SUBSTR(foo,3,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,3,13)
       WHEN SUBSTR(foo,4,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,4,13)
       WHEN SUBSTR(foo,5,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,5,13)
       WHEN SUBSTR(foo,6,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,6,13)
       WHEN SUBSTR(foo,7,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,7,13)
       WHEN SUBSTR(foo,8,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,8,13)
       WHEN SUBSTR(foo,9,13) REGEXP '^[0-9]{13}$' THEN SUBSTR(foo,9,13)
       END AS digits
  FROM t

-------------------
1938420985390
1234812934810
(NULL)
(NULL)
1234073927357
(NULL) 

No, it's not pretty. But you should be able to extend this to effectively "scan" a string of reasonable length.

NOTE: The regular expression is checking that the whole 13 character substring consists of exactly 13 characters, each of the characters is a decimal digit (0 thru 9).

这篇关于从不规则格式的字符串中间返回数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！