SQL 使用 Oracle regexp 查找精确长度为 3 的数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20163742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find a number with Oracle regexp that has an exact length of 3
提问by robert lennon
I want to get only 456.
我只想得到 456。
I tried this regular expression, but I got all numbers which contain 3 digit characters, 456, 46354376, etc.
我试过这个正则表达式，但我得到了所有包含 3 个数字字符的数字，456、46354376 等。
select regexp_substr(MY_COLUMN,'([[:digit:]]{3})') from MY_TABLE
回答by MT0
I'm not quite clear on whether you want exact matches or sub-string matches so I've included various options:
我不太清楚您是想要完全匹配还是子字符串匹配，因此我提供了各种选项：
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
CREATE TABLE MY_TABLE (MY_COLUMN) AS
SELECT '456' FROM DUAL
UNION ALL SELECT '12345678' FROM DUAL
UNION ALL SELECT 'abc 123 def 456' FROM DUAL;
Query 1:
查询 1：
If you only want rows where MY_COLUMNcontains exactly a 3-digit number then you can just use your regular expression wrapped in the start-string (^) and end-string ($) anchors:
如果您只想要MY_COLUMN包含 3 位数字的行，那么您可以使用包含在开始字符串 ( ^) 和结束字符串 ( $) 锚点中的正则表达式：
SELECT MY_COLUMN
FROM MY_TABLE
WHERE REGEXP_LIKE( MY_COLUMN, '^[[:digit:]]{3}$' )
结果：
| MY_COLUMN |
|-----------|
| 456 |
Query 2:
查询 2：
Or, if you are using Oracle 11g then you can use the less verbose PERL syntax:
或者，如果您使用的是 Oracle 11g，那么您可以使用更简洁的 PERL 语法：
SELECT MY_COLUMN
FROM MY_TABLE
WHERE REGEXP_LIKE( MY_COLUMN, '^\d{3}$' )
结果：
| MY_COLUMN |
|-----------|
| 456 |
Query 3:
查询 3：
If you want to extract the first 3-digit number from the column (where it can have surrounding text or more digits), then:
如果要从列中提取第一个 3 位数字（其中可以包含周围文本或更多数字），则：
SELECT MY_COLUMN,
REGEXP_INSTR( MY_COLUMN, '\d{3}' ),
REGEXP_SUBSTR( MY_COLUMN, '\d{3}' )
FROM MY_TABLE
WHERE REGEXP_LIKE( MY_COLUMN, '\d{3}' )
结果：
| MY_COLUMN | REGEXP_INSTR(MY_COLUMN,'\D{3}') | REGEXP_SUBSTR(MY_COLUMN,'\D{3}') |
|-----------------|---------------------------------|----------------------------------|
| 456 | 1 | 456 |
| 12345678 | 1 | 123 |
| abc 123 def 456 | 5 | 123 |
Query 4:
查询 4：
If you want to extract the first exactly 3-digit number from the column then:
如果要从列中提取第一个恰好 3 位数的数字，则：
SELECT MY_COLUMN,
REGEXP_SUBSTR( REGEXP_SUBSTR( MY_COLUMN, '(^|\D)\d{3}(\D|$)' ), '\d{3}' ) AS match
FROM MY_TABLE
WHERE REGEXP_LIKE( MY_COLUMN, '(^|\D)\d{3}(\D|$)' )
结果：
| MY_COLUMN | MATCH |
|-----------------|-------|
| 456 | 456 |
| abc 123 def 456 | 123 |
Query 5:
查询 5：
If you want to extract all the non-overlapping 3-digit numbers from the column (where it can have surrounding text), then:
如果要从列中提取所有不重叠的 3 位数字（其中可以包含周围文本），则：
WITH re_counts AS (
SELECT MY_COLUMN,
REGEXP_COUNT( MY_COLUMN, '\d{3}' ) AS re_count
FROM MY_TABLE
)
,indexes AS (
SELECT LEVEL AS "index"
FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX( re_count) FROM re_counts)
)
SELECT MY_COLUMN,
"index",
REGEXP_SUBSTR( MY_COLUMN, '\d{3}', 1, "index" )
FROM re_counts
INNER JOIN
indexes
ON ("index" <= re_count)
ORDER BY MY_COLUMN, "index"
结果：
| MY_COLUMN | INDEX | REGEXP_SUBSTR(MY_COLUMN,'\D{3}',1,"INDEX") |
|-----------------|-------|--------------------------------------------|
| 12345678 | 1 | 123 |
| 12345678 | 2 | 456 |
| 456 | 1 | 456 |
| abc 123 def 456 | 1 | 123 |
| abc 123 def 456 | 2 | 456 |
Query 6:
查询 6：
If you want to extract all the sub-matches which are exactly 3-digit numbers then:
如果您想提取所有正好是 3 位数字的子匹配项，则：
WITH re_counts AS (
SELECT MY_COLUMN,
REGEXP_COUNT( MY_COLUMN, '(^|\D)\d{3}(\D|$)' ) AS re_count
FROM MY_TABLE
)
,indexes AS (
SELECT LEVEL AS "index"
FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX( re_count) FROM re_counts)
)
SELECT MY_COLUMN,
"index",
REGEXP_SUBSTR( REGEXP_SUBSTR( MY_COLUMN, '(^|\D)\d{3}(\D|$)', 1, "index" ), '\d{3}' ) AS match
FROM re_counts
INNER JOIN
indexes
ON ("index" <= re_count)
ORDER BY MY_COLUMN, "index"
结果：
| MY_COLUMN | INDEX | MATCH |
|-----------------|-------|-------|
| 456 | 1 | 456 |
| abc 123 def 456 | 1 | 123 |
| abc 123 def 456 | 2 | 456 |
Query 7:
查询 7：
If you want to extract all 3-digit numbers from a column regardless of whether those matches partially overlap or not then:
如果您想从一列中提取所有 3 位数字，而不管这些匹配是否部分重叠，那么：
WITH positions AS (
SELECT LEVEL AS pos
FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX( LENGTH( MY_COLUMN ) - 2 ) FROM MY_TABLE )
)
SELECT MY_COLUMN,
pos,
SUBSTR( MY_COLUMN, pos, 3 )
FROM MY_TABLE
INNER JOIN
positions
ON (pos <= LENGTH( MY_COLUMN ) - 2 )
WHERE REGEXP_LIKE( SUBSTR( MY_COLUMN, pos, 3 ), '^\d{3}$' )
ORDER BY MY_COLUMN, pos
结果：
| MY_COLUMN | POS | SUBSTR(MY_COLUMN,POS,3) |
|-----------------|-----|-------------------------|
| 12345678 | 1 | 123 |
| 12345678 | 2 | 234 |
| 12345678 | 3 | 345 |
| 12345678 | 4 | 456 |
| 12345678 | 5 | 567 |
| 12345678 | 6 | 678 |
| 456 | 1 | 456 |
| abc 123 def 456 | 5 | 123 |
| abc 123 def 456 | 13 | 456 |
回答by Ben
You're sub-stringing the number... if you're searching for a number that is exactly 3 digits then use LENGTH():
您正在对数字进行子串化...如果您正在搜索正好为 3 位数的数字，则使用LENGTH()：
select * from my_table where length(my_column) = 3
This presupposes that you've stored your numbers in a NUMBER column. If there might be characters in there as well use REGEXP_LIKE()and ensure you tie the regular expression to the beginning (with the ^operator) and end ($) of the string
这假定您已将数字存储在 NUMBER 列中。如果那里可能有字符，请使用REGEXP_LIKE()并确保将正则表达式绑定到字符串的开头（使用^运算符）和结尾（$）
select * from my_table where regexp_like( my_column, '^[[:digit:]]{3}$')
You're not checking to see whether your number is at both the beginning and end of the string so you're getting everything with 3 numbers.
您没有检查您的号码是否在字符串的开头和结尾，因此您将获得 3 个数字的所有内容。
回答by Casimir et Hippolyte
You can use this pattern:
您可以使用此模式：
(^|[^[:digit:]])([[:digit:]]{3})($|[^[:digit:]])
where [^[:digit:]]is all that is not a digitand ^, $are start and end of the string anchors.
where [^[:digit:]]is all that is not a digitand ^,$是字符串锚点的开始和结束。
