Home » SQL & PL/SQL » SQL & PL/SQL » characters and numeric count in a String
characters and numeric count in a String [message #222119] Thu, 01 March 2007 23:37 Go to next message
rajesh4851
Messages: 89
Registered: January 2007
Member
Hi,
I need the count of numbers(1,23...) as well as characters in a string.
Please provide either plsql or sql query.

Eg:
string: 'abcd12345'

output should come like:
characters -4
numbers- 5

Thanks & Regards,
Rajesh.
Re: characters and numeric count in a String [message #222122 is a reply to message #222119] Thu, 01 March 2007 23:49 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
Why should we do you homework assignment for you?
Re: characters and numeric count in a String [message #222199 is a reply to message #222119] Fri, 02 March 2007 04:35 Go to previous messageGo to next message
webcomsystems
Messages: 4
Registered: February 2007
Location: UK
Junior Member
Like the guy above said, we wont do it for you.

But you need something along the lines of...

1. Loop thru the text character by character and count the numbers using ASCII().
2. If ASCII() between 48 and 57 then its a number.
3. anything else is a character.
4. total these up and output your answer using DBMS_OUTPUT.

Hope this helps.

D Drewette
http://www.oracleunittesting.com
Best Practices in Unit Testing on Oracle Databases

[Updated on: Fri, 02 March 2007 04:35]

Report message to a moderator

Re: characters and numeric count in a String [message #222211 is a reply to message #222199] Fri, 02 March 2007 05:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could translate all the numbers into spaces, replace all the spaces with nulls, and measure the difference in length of the strings.
Quick and easy.
Re: characters and numeric count in a String [message #222327 is a reply to message #222199] Fri, 02 March 2007 20:41 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

select length(replace
(translate(:var,'abcdefghijklmnopqrstuvxyz','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),
'a','')) numbers ,length(:var)-length(replace
(translate(:var,'abcdefghijklmnopqrstuvxyz','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'),
'a','')) as characters from dual;
Re: characters and numeric count in a String [message #222339 is a reply to message #222119] Sat, 03 March 2007 00:09 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Taking into account upper & lower case characters and numbers (did not include special characters since you didn't mention it):
SQL> variable var varchar2(20);
SQL> exec :var := 'ABcd12345';

PL/SQL procedure successfully completed.

SQL> select length(translate(lower(:var),' abcdefghijklmnopqrstuvwxyz',' ')) numbers
  2     , length(translate(:var,' 1234567890',' ')) characters
  3  from dual;

   NUMBERS CHARACTERS
---------- ----------
         5          4

On 10g:
SQL> select length(regexp_replace(:var, '\D')) numbers
  2       ,length(regexp_replace(:var, '\d')) characters
  3  from dual;

   NUMBERS CHARACTERS
---------- ----------
         5          4

[Updated on: Sat, 03 March 2007 00:26]

Report message to a moderator

Re: characters and numeric count in a String [message #222535 is a reply to message #222119] Mon, 05 March 2007 05:38 Go to previous messageGo to next message
t_santhi
Messages: 2
Registered: June 2006
Junior Member
Hi,

SELECT LENGTH(TRANSLATE('&a','0123456789',' ')) chars,
( LENGTH('&a') - LENGTH(TRANSLATE('&a','0123456789',' '))) numbers FROM dual

Re: characters and numeric count in a String [message #222682 is a reply to message #222535] Mon, 05 March 2007 22:40 Go to previous message
rajesh4851
Messages: 89
Registered: January 2007
Member
Thankyou all.
Its working.
Actually it is the problem facing in my current project.

Thanks & Regards,
Rajesh.
Previous Topic: to parse a file name
Next Topic: Autogenerate number?
Goto Forum:
  


Current Time: Wed Dec 07 15:00:24 CST 2016

Total time taken to generate the page: 0.10740 seconds