asked on

Oracle query for delimited values

Hi experts,
I have oracle 10g table for _Files which has a _filename field
sample entries can be
f1.xlsx
file2.doc
fl.h
testfile.abcxyz

I need a query that gives only the extensions . in this case the result should give
.xlsx
.doc
.h
.abcxyz

Your help is appreciated

Kevin Cross

I don't have Oracle on this machine to test but technique I have used before is to reverse string then find first position of "." then use that number to take the right most characters of the original string.

substr( your_string, -instr( reverse( your_string ), '.', 1, 1 ) )

Open in new window

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

@Author - Please add Oracle Database as Topic for future reads.

Kevin Cross

What happens if file name has a '.' in it? Deal with that a lot, so again highly recommend you reverse the string and find the '.' from the back so you do not trip yourself up when the data is not as clean as it is today. Just my humble opinion, though.

Pawan Kumar

You can also use a bit simple like below -

REGEXP_SUBSTR

WITH CTE AS
(
    SELECT 'f1.xlsx' fname FROM DUAL UNION ALL
    SELECT 'file2.doc' fname FROM DUAL UNION ALL
    SELECT 'fl.h' fname FROM DUAL UNION ALL
    SELECT 'testfile.abcxyz' fname FROM DUAL
)
SELECT REGEXP_SUBSTR(fname,'(\.)\S*') Extension from CTE

Open in new window

Output

 	EXTENSION
1	.xlsx
2	.doc
3	.h
4	.abcxyz

Open in new window

Pawan Kumar

For Multiple ..... (dots) in a file name is not a problem.. below is the perfect one... Do not reverse the string unnecessarily.

example - SELECT 't...est..file.abcxyz' fname FROM DUAL

WITH CTE AS
(
    SELECT 'f1.xlsx' fname FROM DUAL UNION ALL
    SELECT 'file2.doc' fname FROM DUAL UNION ALL
    SELECT 'fl.h' fname FROM DUAL UNION ALL
    SELECT 'testfile.abcxyz' fname FROM DUAL UNION ALL
    SELECT 't...est..file.abcxyz' fname FROM DUAL
    
)
SELECT SUBSTR(fname,INSTR(fname,'.',-1),LENGTH(fname)) Extension from CTE

Open in new window

Output

 	EXTENSION
1	.xlsx
2	.doc
3	.h
4	.abcxyz
5	.abcxyz

Open in new window

Kevin Cross

If you can point to a performance issue it creates, I will agree with you. Never had an issue with it.

Alex [***Alex140181***]

below is the perfect one

Says who?! This always depends on the specific needs!

If you can point to a performance issue it creates, I will agree with you. Never had an issue with it.

Me, neither and he will not be able to show us ;-)
I'm also using this function once and a while and it is at least as fast as any other combo of built-in functions (except the regex calls, they are not so fast at all)...