如何使用Oracle SQL中的REGEX在数据库列的行中检索特定的字符位置?

What Oracle SQL query could return the second, third and fourth positions of characters contained within rows of a specific column using the REGEXP_SUBSTR method instead of using SUBSTR method like my example provided below?

什么Oracle SQL查询可以使用REGEXP_SUBSTR方法返回特定列中包含的第二、第三和第四行字符的位置，而不像下面的示例那样使用SUBSTR方法?

SELECT SUBSTR(city,2,3) AS "2nd, 3rd, 4th"
FROM student.zipcode;`

1 个解决方案

#1

One way that works for me (with test data) is:

对我(测试数据)有用的一种方法是:

SELECT REGEXP_SUBSTR(city, '\S{3}', 2) AS partial FROM student.zipcode;

Note that this is set to find three non-whitespace characters beginning at the second position of the string.

注意，这里设置为查找从字符串的第二个位置开始的三个非空白字符。

You could also use:

你也可以使用:

SELECT REGEXP_SUBSTR(city, '.{3}', 2) AS partial FROM student.zipcode;

which will instead match any three characters in the 2nd to 4th position.

相反，它会匹配2到4位的任何3个字符。

However, I'm not sure what advantage this has over simply:

然而，我不确定这比简单的优势是什么:

SELECT SUBSTR(city,2,3) AS partial FROM student.zipcode;

The REGEXP_INSTR function is not what you want, as it returns an index (position number) for the search item in the searched string. You can read about it here: http://www.techonthenet.com/oracle/functions/regexp_instr.php

REGEXP_INSTR函数不是您想要的，因为它返回搜索字符串中搜索项的索引(位置号)。您可以在这里阅读:http://www.techonthenet.com/oracle/functions/regexp_提高

#1