Link to home
Start Free TrialLog in
Avatar of victoriaharry
victoriaharry

asked on

splitting values from oracle fields

I have a field in a oracle table which has values in the format

Joe Bloggs - 17/11/2013 10:38:27 AM
Joe Bloggs - 17/10/2014 10:38:27 AM
Joe Bloggs - 17/10/2014 10:50:27 AM
Joe Bloggs - 18/10/2014 10:12:27 AM
Peter Jones - 18/10/2014 09:12:27 AM
Peter Jones - 20/10/2014 09:12:27 AM


What I want to do is a count of all the names in this field which have 2014 in them.
For example, using the values above I would end up with

Joe Bloggs 3
Peter Jones 2

Hope this makes sense and any ideas on how to achieve this would be very appreciated

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Haris Dulic
Haris Dulic
Flag of Austria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Correction to the SQL above, the group by is wrong.

Also don't need the rtrim.
 select substr(col1,1,INSTR(col1,' - ')) as name, count(1)
  from tab1
  where col1 like '%2014%'
  group by substr(col1,1,INSTR(col1,' - '))
/

Open in new window

Mind if I ask why you selected an answer that doesn't produce the expected results?