Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

splitting values from oracle fields

Posted on 2014-10-19
3
402 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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 Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

809 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