SQL正则表达式编号后不带字符串 [英] SQL Regex number not followed by a string
问题描述
首先让我提到这是一个经过充分讨论的问题，我经历了包括这两个在内的多个线程-最接近的匹配一个正则表达式匹配一个子字符串，该子字符串后面没有某个其他子字符串，但他们确实做到了无法解决我的问题.
Let me first mention that this a well discussed problem and I have gone through several thread including these two - which are closest match Regex to match a string not followed by some string and A regex to match a substring that isn't followed by a certain other substring but they did not solve my problem.
我有一些包含容量和数量的字符串，它们具有几种不同的格式-如下所述，例如6 X 200毫升平均6包，每200毫升.在此示例中，我只想提取像6这样的数量
I have strings containing volume and quantity in several different formats -mentioned below, e.g. 6 X 200ml mean 6 packs of 200 milliliters each. I want to extract only the quantity like 6 in the this example
示例
- blah 6 X 200ml-6
- blah 200 mlX 6-6
- blah x 5000 ml-0或更好1
- blah x 500000ml-0或更好1
- blah 5mlX10-10
- blah 500 mlX 10-10
这是我到目前为止一直没有成功的尝试
This is what I've tried so far without any success
(X\s*\d+|\d+\s*X)(?!\s*ml)
它也匹配情况3和情况4，不应匹配.我也可以提取带有乘号(例如6 X而不是6 X)的数量，例如6.我可以替换它.
it matches case #3 and 4 as well which shouldn't be matched. I am also fine with extracting quantity like 6 with multiplication sign e.g 6 X instead of just 6. I can replace it.
推荐答案
您没有在问题中提及正在使用的数据库.
SQL标准不包含正则表达式，因此每个数据库都有其自己的regexp引擎实现，它们各自不同，并且不支持正则表达式的许多功能，例如环顾四周.在不知道您正在使用的确切数据库的情况下，很难为您提供帮助.
You didn't mention a database you are using in the question.
The SQL standard does not include regular expressions, so each database has its own regexp engine implementation, each of them is different and does not support many features of regular expressions, like lookarounds. It is hard to help you without knowing an exact database you are using.
以下是两个简单示例，说明如何使用
在Oracle和PostgreSQL数据库中解决此问题但是这不适用于Oracle/PostgreSQL以外的其他数据库.
对Oracle的查询:
在线演示: http://sqlfiddle.com/#!4/599c41/5
The below are two simple examples how this problem can be solved in Oracle and PostgreSQL databases using
But this won't work on other databases than Oracle/PostgreSQL.
A query for Oracle:
Online demo: http://sqlfiddle.com/#!4/599c41/5
select t.*,
regexp_substr( regexp_replace( "text", '\d+\s*ml', '///' ), '\d+' ) as x
from table1 t;
| text | X |
|-------------------|--------|
| blah 6 X 200ml | 6 |
| blah 200 mlX 6 | 6 |
| blah x 5000 ml | (null) |
| blah x 500000ml | (null) |
| blah 5mlX10 | 10 |
| blah 500 mlX 10 | 10 |
如果要将NULL替换为0或1，可以按以下方式使用CASE EXPRESSION:
If you want to replace NULLs by 0 or 1, you can use CASE EXPRESSIONs in this way:
select t.*,
CASE WHEN regexp_substr( regexp_replace( "text", '\d+\s*ml', '///' ), '\d+' )
IS NULL THEN '1' /* or 0 */
ELSE regexp_substr( regexp_replace( "text", '\d+\s*ml', '///' ), '\d+' )
END as x
from table1 t;
| text | X |
|-------------------|----|
| blah 6 X 200ml | 6 |
| blah 200 mlX 6 | 6 |
| blah x 5000 ml | 1 |
| blah x 500000ml | 1 |
| blah 5mlX10 | 10 |
| blah 500 mlX 10 | 10 |
PostgreSQL查询:
select t.*,
substring( regexp_replace( "text", '\d+\s*ml', '///') from '\d+' ) as x
from table1 t;
| text | x |
|-------------------|--------|
| blah 6 X 200ml | 6 |
| blah 200 mlX 6 | 6 |
| blah x 5000 ml | (null) |
| blah x 500000ml | (null) |
| blah 5mlX10 | 10 |
| blah 500 mlX 10 | 10 |
在线演示: http://sqlfiddle.com/#!17/b003b/1
这篇关于SQL正则表达式编号后不带字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
