Home » SQL & PL/SQL » SQL & PL/SQL » funtion to count characters
funtion to count characters [message #198809] Wed, 18 October 2006 11:18 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Hi:

anyone knows any function to count how many times a character appears in a string??

for example

cadena1 / cadena2 / cadena3

if i look the charcater "/" the result should be = 2

Thanks
Alex
Re: funtion to count characters [message #198815 is a reply to message #198809] Wed, 18 October 2006 11:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
select length('vamsi|vamsi|kasina|') - 
length(replace('vamsi|vamsi|kasina|','|')) from dual;


By
Vamsi
Re: funtion to count characters [message #198816 is a reply to message #198815] Wed, 18 October 2006 12:07 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

but if i have

length(replace('vamsi|vamsi|kasina|','|')) from dual;

in a field of my database this will replace the character "|", no?

and i only want to count it!
Re: funtion to count characters [message #198818 is a reply to message #198816] Wed, 18 October 2006 12:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The query will not update any data - it is comparing the length of two strings (the original string minus the length of the string with the character in question removed). The result is a number indicating the number of times the character appears.
Re: funtion to count characters [message #198819 is a reply to message #198816] Wed, 18 October 2006 12:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
No, it won't.

If at all you want to change that in the database, you should use update.
As we are not using any update here, it won't get changed in the database. The replacement happens only virtually.

By,
Vamsi.
Re: funtion to count characters [message #198820 is a reply to message #198819] Wed, 18 October 2006 12:26 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

ok

thanks guys
Re: funtion to count characters [message #241702 is a reply to message #198809] Wed, 30 May 2007 08:00 Go to previous messageGo to next message
goyalnav2002
Messages: 1
Registered: May 2007
Junior Member
Another way to do is create your own function

CREATE FUNCTION NUM_CHARS(INSTRING VARCHAR2, INPATTERN VARCHAR2)
RETURN NUMBER
IS
COUNTER NUMBER;
NEXT_INDEX NUMBER;
STRING VARCHAR2(2000);
PATTERN VARCHAR2(2000);
BEGIN
COUNTER := 0;
NEXT_INDEX := 1;
STRING := LOWER(INSTRING);
PATTERN := LOWER(INPATTERN);
FOR I IN 1 .. LENGTH(STRING) LOOP
IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)
AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN
COUNTER := COUNTER+1;
END IF;
NEXT_INDEX := NEXT_INDEX+1;
END LOOP;
RETURN COUNTER;
END;

Test this function by executing following select statement

select num_chars('india',i) from dual;

it will return 2
Re: funtion to count characters [message #241706 is a reply to message #241702] Wed, 30 May 2007 08:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There are numerous other ways to make it even more difficult.
The length(replace) is far more elegant and easy to maintain.
Re: funtion to count characters [message #241708 is a reply to message #241702] Wed, 30 May 2007 08:11 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Let's put this one to sleep, shall we?

http://www.orafaq.com/forum/fa/448/0/

Closed.

Look at the date.

MHE
Previous Topic: Querry taking a lot of time
Next Topic: Find the spaces in a word
Goto Forum:
  


Current Time: Mon Dec 05 21:17:49 CST 2016

Total time taken to generate the page: 0.21547 seconds