Finding the count of characters and numbers in a string

Question

Hi I have a table test as below

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;

Its executing fine giving the output

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

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

Answer 1

@alfasin answer is good, but if you're using 11g then it can get simpler:

select name,
REGEXP_count(name,'\d') as num_count,
REGEXP_count(name,'[a-zA-Z]') as char_count,
from test6;

Answer 2

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.

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

and digits:
select LENGTH(REGEXP_REPLACE('abcd12345','[a-zA-Z]')) from dual

Or, in your case:

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

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...