Home » SQL & PL/SQL » SQL & PL/SQL » Counting how many times a certain character appears
Counting how many times a certain character appears [message #202618] Fri, 10 November 2006 10:02 Go to next message
ashlewis
Messages: 7
Registered: November 2006
Junior Member
I need a statement to count how many times a comma appears in a cell.

For example if a cell contains this:
"volvo, honda"

i need to count how many commas appear, so for this the answer would be 1.

anyone know a statement for this, it would help me alot.thanks
Re: Counting how many times a certain character appears [message #202623 is a reply to message #202618] Fri, 10 November 2006 10:08 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select length('volvo, honda') - length(replace('volvo, honda',',')) "# of commas" from dual;

# of commas
-----------
          1

Re: Counting how many times a certain character appears [message #202628 is a reply to message #202618] Fri, 10 November 2006 10:23 Go to previous messageGo to next message
ashlewis
Messages: 7
Registered: November 2006
Junior Member
thank you very much, this is what i needed. however im actually looking to count the number of cars, so i will need to add one to the returned value, so "volvo, honda" should return 2.sorry i didnt mention this previously.
also i need them in a table format so it returns a table of how many cars are in each cell

eg

NAME | CARS
john volvo
david volvo, ford
sue rover

would make a table

NAME | number of cars
john 1
david 2
sue 1


i hope ive explained myself better, thanks
Re: Counting how many times a certain character appears [message #202630 is a reply to message #202628] Fri, 10 November 2006 10:35 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from name_cars;

NAME                 CARS
-------------------- --------------------
John                 Volvo
David                Volvo, Ford
Sue                  Rover

SQL> create table cars_per_person as
  2  select name, (length(cars) - length(replace(cars, ',')) + 1) "number of cars" from name_cars;

Table created.

SQL> select * from cars_per_person;

NAME                 number of cars
-------------------- --------------
John                              1
David                             2
Sue                               1
Re: Counting how many times a certain character appears [message #202720 is a reply to message #202618] Sat, 11 November 2006 05:09 Go to previous messageGo to next message
ashlewis
Messages: 7
Registered: November 2006
Junior Member
Thanks very much, ive just realised my database needs to be able to deal with NULL values, for example if a person has no cars wont this code return 1? is there anyway to overcome this problem? say maybe an if statement or somthing?
Re: Counting how many times a certain character appears [message #202721 is a reply to message #202720] Sat, 11 November 2006 05:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You know, you might try some things yourself.
for example, your previous question involved simply adding 1 to the result.
Try to think how you could handle nulls; try some alternatives, show it here and we will help you.
That we you will actually learn from it!
Re: Counting how many times a certain character appears [message #202724 is a reply to message #202618] Sat, 11 November 2006 05:43 Go to previous messageGo to next message
ashlewis
Messages: 7
Registered: November 2006
Junior Member
Razz
i tried fiddling with the inital code given to me but because i didnt actually know what it was doing i didnt know where to add the +1, having studied the code more i think i understand what its doing.

So would i be right to make some sort of if statement? or would i be going down the wrong track?
Re: Counting how many times a certain character appears [message #202740 is a reply to message #202724] Sat, 11 November 2006 09:16 Go to previous messageGo to next message
ashlewis
Messages: 7
Registered: November 2006
Junior Member
whey! never mind all sorted, thanks for the help guys!
Re: Counting how many times a certain character appears [message #202745 is a reply to message #202740] Sat, 11 November 2006 11:21 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Ebrian,

You gave a very beatifull answer.

Bye
Ashu
Previous Topic: ORA-04091: table XXXX is mutating, trigger/function may not see it
Next Topic: stopping duplication
Goto Forum:
  


Current Time: Wed Dec 07 20:40:15 CST 2016

Total time taken to generate the page: 0.11227 seconds