Solved

splitting values from oracle fields

Posted on 2014-10-19
3
413 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
[X]
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
  • 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 77

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 77

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

732 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