从 varchar 中选择元音，Oracle PL/SQL [英] Select vowels from a varchar, Oracle PL/SQL

问题描述

我正在尝试提取 varchar 中包含的元音数，我一直在谷歌上四处寻找，但没有成功.

I'm trying to pull up the count of the vowels contained in a varchar, I've been looking around in google, no success though.

有人可以帮我处理一下吗?

Can anyone give me a hand with this one?

推荐答案

如果您使用的是 Oracle 11g，则可以使用 REEXP_COUNT 函数来确定匹配模式的内容.

If you're using Oracle 11g, you can use the REXEXP_COUNT function to determine what matches the pattern.

SQL> select regexp_count('andrew', '[aeiou]', 1, 'i') as vowels
  2    from dual;

    VOWELS
----------
         2

第一个参数是你要匹配的字符串，'andrew'.

The first parameter is the string you want to match, 'andrew'.

第二个参数是匹配模式，在本例中为 [aeiou].[] 表示字符列表；解析器以任何顺序匹配此列表中的所有字符.

The second parameter is the match pattern, in this case [aeiou]. The [] indicates a character list; the parser matches any and all characters inside this list in any order.

第三个参数 1 是开始位置，指示 Oracle 应该开始搜索匹配项的字符串的位置索引.仅包含它以便我可以使用第四个参数.

The third parameter, 1, is the start position indicating the positional index of the string where Oracle should start searching for a match. It's included solely so I can use the fourth parameter.

第四个参数是匹配参数，'i'表示我要做不区分大小写的匹配.这就是字符列表不是[aeiouAEIOU]的原因.

The fourth parameter is a match parameter, 'i' indicates that I want to do case insensitive matching. This is the reason why the character list is not [aeiouAEIOU].

如果您使用的是 10g，则 REGEXP_COUNT 不存在.在这种情况下，您可以使用更精确的 Annjawan 的解决方案 和 REGEXP_REPLACE.

If you're using 10g then REGEXP_COUNT doesn't exist. In this case you could use a more exact version of Annjawan's solution with REGEXP_REPLACE.

SQL> select length(regexp_replace('andrew','[^aeiou]', '', 1, 0, 'i')) as vowels
  2    from dual;

    VOWELS
----------
         2

克拉(^)表示不，即替换字符串'andrew'中不在字符列表[aeiou]中的每个字符 带有空字符串.下一个参数再次是起始位置.第五个参数 0 表示您要替换每个匹配的模式，我再次使用了匹配参数 'i' 表示不区分大小写的匹配.

The carat (^) indicates a not, i.e. the replaces every character in the string 'andrew' that is not in the character list [aeiou] with the empty string. The next parameter, once again, is the start position. The fifth parameter, 0 indicates that you want to replace every occurrence of the pattern that matches and once again I've used the match parameter 'i' to indicate case insensitive matching.

Gaurav 的回答不正确.这是因为在字符列表中他包含了逗号.请记住，如果可用，字符列表中的所有内容 都会匹配.所以，如果我在你的字符串中引入一个逗号，你的字符串中就会有 3 个元音":

Gaurav's answer is incorrect. This is because within the character list he has included comma's. Remember that everything within the character list get's matched if it is available. So, if I introduce a comma into your string you'll have 3 "vowels" in your string:

SQL> select regexp_count('an,drew','[a,e,i,o,u,A,E,I,O,U]' ) as vowels
  2    from dual;

    VOWELS
----------
         3

正则表达式不是简单的野兽，我强烈建议阅读尝试它们时的文档.

Regular expressions are not simple beasts and I would highly recommend reading the documentation when attempting them.

这篇关于从 varchar 中选择元音，Oracle PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！