Oracle：一个查询，它计算字符串中所有非字母数字字符的出现次数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4831362/
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
Oracle: a query, which counts occurrences of all non alphanumeric characters in a string
提问by Moz
What would be the best way to count occurrences of all non alphanumeric characters that appear in a string in an Oracle database column.
计算 Oracle 数据库列中字符串中出现的所有非字母数字字符的出现次数的最佳方法是什么。
When attempting to find a solution I realised I had a query that was unrelated to the problem, but I noticed I could modify it in the hope to solve this problem. I came up with this:
在尝试找到解决方案时，我意识到我有一个与问题无关的查询，但我注意到我可以修改它以希望解决这个问题。我想出了这个：
SELECT COUNT (*), SUBSTR(TITLE, REGEXP_INSTR(UPPER(TITLE), '[^A-Z,^0-9]'), 1)
FROM TABLE_NAME
WHERE REGEXP_LIKE(UPPER(TITLE), '[^A-Z,^0-9]')
GROUP BY SUBSTR(TITLE, REGEXP_INSTR(UPPER(TITLE), '[^A-Z,^0-9]'), 1)
ORDER BY COUNT(*) DESC;
This works to find the FIRST non alphanumeric character, but I would like to count the occurrences throughout the entire string, not just the first occurrence. E. g. currently my query analysing "a (string)" would find one open parenthesis, but I need it to find one open parenthesis and one closed parenthesis.
这可以找到第一个非字母数字字符，但我想计算整个字符串中的出现次数，而不仅仅是第一次出现。例如 目前我的查询分析“a（字符串）”会找到一个开括号，但我需要它来找到一个开括号和一个闭括号。
采纳答案by Allan
The best option, as you discovered is to use a PL/SQL procedure. I don't think there's any way to create a regex expression that will return multiple counts like you're expecting (at least, not in Oracle).
正如您所发现的，最好的选择是使用 PL/SQL 过程。我不认为有任何方法可以创建一个正则表达式，它会像您期望的那样返回多个计数（至少，不是在 Oracle 中）。
One way to get around this is to use a recursive query to examine each character individually, which could be used to return a row for each character found. The following example will work for a single row:
解决此问题的一种方法是使用递归查询单独检查每个字符，这可用于为找到的每个字符返回一行。以下示例适用于单行：
with d as (
select '(1(2)3)' as str_value
from dual)
select char_value, count(*)
from (select substr(str_value,level,1) as char_value
from d
connect by level <= length(str_value))
where regexp_instr(upper(char_value), '[^A-Z,^0-9]'), 1) <> 0
group by char_value;
回答by IK.
There is an obscure Oracle TRANSLATE function that will let you do that instead of regexp:
有一个晦涩的 Oracle TRANSLATE 函数可以让您代替正则表达式执行此操作：
select a.*,
length(translate(lower(title),'.0123456789abcdefghijklmnopqrstuvwxyz','.'))
from table_name a
回答by Chandu
Try this:
尝试这个：
SELECT a.*, LENGTH(REGEXP_REPLACE(TITLE, '[^a-zA-Z0-9]'), '')
FROM TABLE_NAME a
