• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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
0
victoriaharry
Asked:
victoriaharry
  • 2
1 Solution
 
Haris DjulicCommented:
Hello,

here is the code:
 select rtrim(substr(column_name,1,INSTR(column_name,' - '))) as name, count(1)
  from table_name
  where column_name like '%2014%'
  group by column_name

Open in new window


you just need to replace the column_name and table_name in my code with your real values..
0
 
slightwv (䄆 Netminder) Commented:
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

0
 
slightwv (䄆 Netminder) Commented:
Mind if I ask why you selected an answer that doesn't produce the expected results?
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now