CalmSoul
asked on
count last three characters of the record
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?
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?
Regular expression version:
select regexp_substr(col1,'[^.]+$') , count(*)
from tab1
group by regexp_substr(col1,'[^.]+$')
/
ASKER
How is this solution on performance ... ?
>>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.
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,'.'
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>This would handle a case where the file name contained a period
Excellent catch!
Excellent catch!
ASKER
select substr(col1,instr(col1,'.', -1)+1), count(*)
from tab1
group by substr(col1,instr(col1,'.', -1)+1)
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.
>>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.
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.
ASKER
with regular experssion I am getting following error
Lookup Error
ORA-00904: invalid column name
ASKER
I think regular expression doesn't work in oracle 8i
>>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.
Probably not.
Try the ones from johnsone. If those fail, please post additional data and expected results.
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.
Open in new window