Home » SQL & PL/SQL » SQL & PL/SQL » Count characters
Count characters [message #574119] Mon, 07 January 2013 05:45 Go to next message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member

Hi All,

How can I find out that exact count of '~'? Please help.

 SELECT NVL(LENGTH('~~~~~~~~~~~~~~~~~')-LENGTH(REPLACE('~~~~~~~~~~~~~~~~~','~','')),0) result
FROM dual;


Regards,
Nathan
Re: Removal of Special characters in a string [message #574121 is a reply to message #574119] Mon, 07 January 2013 05:58 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

if don't mind which version are you using..
Re: Removal of Special characters in a string [message #574122 is a reply to message #574121] Mon, 07 January 2013 06:00 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like...
 select  length('~~~~~~~~~~~~~~~~~') - nvl(replace('~~~~~~~~~~~~~~~~~','~',''),0) from dual 

Re: Removal of Special characters in a string [message #574124 is a reply to message #574122] Mon, 07 January 2013 06:06 Go to previous messageGo to next message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member

Hi

the version is
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

it is throwing invalid number when I just modified
 select  length('~~~~~~~~~~~~~xx~~~~') - nvl(replace('~~~~~~~~~~~~~xx~~~~','~',''),0) from dual ;


Regards,
Nathan
Re: Removal of Special characters in a string [message #574127 is a reply to message #574124] Mon, 07 January 2013 06:17 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like....

select count(*), b.item ITEM
      from ( select a.ITEM
               from (select substr( :X,level,1 ) ITEM
               from dual connect by level < length(:X) + 1
                 ) a
           ) b
     group by b.item
     order by b.item; 
Re: Removal of Special characters in a string [message #574131 is a reply to message #574127] Mon, 07 January 2013 06:28 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why don't you simply subtract LENGTHS (not lentgh and suspicious NVL(REPLACE...) and stuff)?
SQL> with test as (select '~~~~~~~~~~~~~xx~~~~' col from dual)
  2  select length(col) - length(replace(col, '~', '')) result
  3  from test;

    RESULT
----------
        17

SQL>
Re: Removal of Special characters in a string [message #574132 is a reply to message #574127] Mon, 07 January 2013 06:28 Go to previous messageGo to next message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member

Hi All,

My requirement is like when both of the variable is having same tilde the it should print 'equal' otherwise 'not equal'.
1.But here the out put is showing like

if i put like
tax VARCHAR2(20) := '~~~~~~~~~~~~~~~~~';
output1
---------
l_tax_comp17xx xx
l_cash_comp17
17
Not equal

if just modified the
tax VARCHAR2(20) := '~~~~~~~~ ~~~~~~~~~';
output2
---------
l_tax_comp19xx 2 xx17
l_cash_comp17
17 17
equal

DECLARE
  tax        VARCHAR2(20)  := '~~~~~~~~~~~~~~~~~';
  cash       VARCHAR2(4000):='10~14~11~98~11~17~98~12~93~22~67~87~75~60~40~15~9~10';
  l_tax_len  NUMBER;
  l_cash_len NUMBER;
  l_tax_rep  NUMBER;
  l_cash_rep NUMBER;
  l_tax_comp NUMBER;
  l_cash_comp number;
BEGIN
  l_tax_len   :=LENGTH(tax);
  l_tax_rep   :=LENGTH(REPLACE(tax,'~',''));
  l_cash_len  :=LENGTH(cash);
  l_cash_rep  :=LENGTH(REPLACE(cash,'~',''));
  l_tax_comp  :=l_tax_len-l_tax_rep;
  l_cash_comp  :=l_cash_len-l_cash_rep;
  dbms_output.put_line('l_tax_comp'||l_tax_len ||'xx ' ||l_tax_rep||' xx' || l_tax_comp);
  dbms_output.put_line('l_cash_comp'|| l_cash_comp);
  IF l_tax_len =l_tax_rep THEN
    l_tax_comp:=l_tax_len;
  END IF;
  IF l_cash_len =l_cash_rep THEN
    l_cash_comp:=l_cash_rep;
  END IF;
    dbms_output.put_line(l_cash_comp|| ' '|| l_tax_comp);
  IF l_tax_comp=l_cash_comp THEN
    dbms_output.put_line('equal');
  ELSE
    dbms_output.put_line('Not equal');
  END IF;
END; 



Please help me..

Regards,
Nathan
Re: Removal of Special characters in a string [message #574134 is a reply to message #574131] Mon, 07 January 2013 06:35 Go to previous messageGo to next message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member

Hi LittleFoot,

Sorry I posted the below code before reading your post.I just modified your code it is returning null,So How can I get 17 counts for this result.Please help..

with test as (select '~~~~~~~~~~~~~~~~~' col from dual)
   select length(col) - length(replace(col, '~', '')) result
  from test


Regards,
Nathan
Re: Removal of Special characters in a string [message #574136 is a reply to message #574134] Mon, 07 January 2013 06:39 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's because the lenght of a null string is null.
A simple nvl will fix it
with test as (select '~~~~~~~~~~~~~~~~~' col from dual)
   select length(col) - nvl(length(replace(col, '~', '')),0) result
  from TEST;
Re: Removal of Special characters in a string [message #574139 is a reply to message #574136] Mon, 07 January 2013 06:46 Go to previous messageGo to next message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member

Hi CookieMonster,

Great Answer and Thanks to All.

The issue seems to got resolved but I have one doubt here,even string contains '~~~~~~~~~~~~~~~~~'. How oracle treats as Null.

Regards,
Nathan
Re: Removal of Special characters in a string [message #574140 is a reply to message #574139] Mon, 07 January 2013 06:53 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because the replace replaces the '~' with ''. If the string only contains '~' then the result of the replace must be null.
Re: Removal of Special characters in a string [message #574141 is a reply to message #574140] Mon, 07 January 2013 07:03 Go to previous message
sss111ind
Messages: 427
Registered: April 2012
Location: India
Senior Member


Yes I got it. Again I am extremely thankfull to all of you.

Regards,
Nathan
Previous Topic: Removal of Special characters in a string
Next Topic: ref cursor with oracle procedure
Goto Forum:
  


Current Time: Fri Apr 18 10:08:30 CDT 2014

Total time taken to generate the page: 0.14089 seconds