Solved

splitting values from oracle fields

Posted on 2014-10-19
3
400 Views
Last Modified: 2014-10-20
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
Comment
Question by:victoriaharry
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40391139
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40391158
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
 
LVL 76

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now