查找字符串中的字符和数字的数量 [英] Finding the count of characters and numbers in a string

问题描述

我有如下表测试

NAME
---------
abc1234
XYZ12789
a12X8b78Y9c5Z

我试图找出字符串中数字和字符的数量为

I try to find out the count of number of numbers and characters in the string as

select name,length(replace(translate(lower(name),'abcdefghijklmnopqrstuvwxyz',' '),'      ','')) as num_count,
length(replace(translate(name,'1234567890',' '),' ','')) as char_count
from test6;

执行得很好，提供输出

NAME    NUM_COUNT   CHAR_COUNT
abc1234         4       3
XYZ12789        5       3
a12X8b78Y9c5Z   7       6

但是我的问题是不给abcdefghijklmnopqrstuvwxyz有什么选择 和1234567890手动

But my question is there any option by not giving the abcdefghijklmnopqrstuvwxyz and 1234567890 manually

推荐答案

如果我正确理解您使用的是Oracle PLSQL，据我所知，没有任何内置"方法(在PLSQL中)计算字符串中的数字/字符数.

If I understand correctly you are using Oracle PLSQL, and as far as I know, there isn't any "built-in" method (in PLSQL) that counts the number of digits/characters in a string.

但是，您可以执行以下操作来计数字符:
select LENGTH(REGEXP_REPLACE('abcd12345','[0-9]')) from dual

But, you can do the following to count characters:
select LENGTH(REGEXP_REPLACE('abcd12345','[0-9]')) from dual

和数字:
select LENGTH(REGEXP_REPLACE('abcd12345','[a-zA-Z]')) from dual

或者，就您而言:

select name,
LENGTH(REGEXP_REPLACE(name,'[a-zA-Z]','')) as num_count,
LENGTH(REGEXP_REPLACE(name,'[0-9]','')) as char_count,
from test6;

对于蜥蜴人比尔(Bill the Lizard):
我的答案已经在Oracle 11g上进行了测试，效果很好！
如果您决定再次删除我的答案，请友好地添加注释以说明原因.我也在聊天室里找你...

For Bill the Lizard:
My answer was tested on Oracle 11g and it works just fine!
If you decide to delete my answer again, please be kind enough to add a comment that explains why. I was also looking for you in the chat rooms...

这篇关于查找字符串中的字符和数字的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！