[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

count last three characters of the record

Posted on 2014-02-27
12
Medium Priority
?
452 Views
Last Modified: 2014-03-01
Hello All:

I need query help... I have following records ....

filename.a01
filename.a02
filename.a03
filename.a04
filename.a05
filename.a06
filename.a01
filename.a08
filename.a01
filename.a10
filename.a11
filename.a12
filename.a13
filename.a01
filename.a15
filename.a16
filename.a17
filename.a18
filename.a19
filename.a04
filename.a21
filename.a22
filename.a23
filename.a24
filename.a04
filename.a26
filename.a27
filename.a28
filename.a29
filename.a04
filename.a31
filename.a32
filename.a33
filename.a34
filename.a35
filename.a36


I need to count all the extents of the filename for example

a36 = 1
a35 = 1

and so on ...

How to query like this?
0
Comment
Question by:CalmSoul
[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
  • 6
  • 4
  • 2
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39893739
Try the example below.  You can use regular expressions if the actual data is more complex but they are pretty expensive operations and should be avoided whenever possible,

 
drop table tab1 purge;

create table tab1(col1 varchar2(20));

insert into tab1 values('filename.a01');
insert into tab1 values('filename.a02');
insert into tab1 values('filename.a03');
insert into tab1 values('filename.a04');
insert into tab1 values('filename.a05');
insert into tab1 values('filename.a06');
insert into tab1 values('filename.a01');
insert into tab1 values('filename.a08');
commit;


select substr(col1,instr(col1,'.')+1), count(*)
from tab1
group by substr(col1,instr(col1,'.')+1)
/

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39893741
Regular expression version:
select regexp_substr(col1,'[^.]+$') , count(*)
from tab1
group by regexp_substr(col1,'[^.]+$')
/

Open in new window

0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39893784
How is this solution on performance ... ?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39893788
>>How is this solution on performance ... ?

First one should be about as fast as I can think to make it.

Depending on how many rows and how often you need to run this query, you can create a function based index (FBI) on substr(col1,instr(col1,'.')+1) and it should fly.

The downside to the FBI is the hit on inserts and updates on the base table.

You will need to experiment to find the balance.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 39894565
I would make a subtle change:

select substr(col1,instr(col1,'.', -1)+1), count(*)
from tab1
group by substr(col1,instr(col1,'.', -1)+1)
/

Open in new window


This would handle a case where the file name contained a period, such as file.name.a01.  The original would process that as name.a01, which doesn't sound like the intent.  This would change to look for the last period in the name and not the first period.

If you wanted to instead look strictly at the last 3 characters of the name, as the post title suggests, then it would be:

select substr(col1,-3), count(*)
from tab1
group by substr(col1, -3)
/

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39894646
>>This would handle a case where the file name contained a period

Excellent catch!
0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39895464
select substr(col1,instr(col1,'.', -1)+1), count(*)
from tab1
group by substr(col1,instr(col1,'.', -1)+1)

Open in new window


Problem with this solution it should also check if "." is present in last 3 charaters. Basicially its counting where ever the period is present in the string ...

So...

Find the period in the string and verify is it the last 3 charaters from the right. If yes, then count.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39895473
>>Problem with this solution it should also check if "." is present in last 3 charaters

You lost me.  Did you try the corrections from johnsone above?  his first one starts at the end of the string and takes from the LAST period.

The second one takes the last three characters no matter what they are.


Try the regular expression one I posted.  It takes anything not a '.' to the end of the string.

We can give you anything you want, we just need to know all the requirements and variations in your data.
0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39895486
with regular experssion I am getting following error

Lookup Error
ORA-00904: invalid column name

Open in new window

0
 
LVL 5

Author Comment

by:CalmSoul
ID: 39895510
I think regular expression doesn't work in oracle 8i
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39895608
>>I think regular expression doesn't work in oracle 8i

Probably not.

Try the ones from johnsone.  If those fail, please post additional data and expected results.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 39897266
I'm lost by the explanation too.  If the ones that I have already posted don't work, please provide better samples of what you are looking for.  Especially post the "hard" cases.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

649 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