user3224907 user3224907 - 4 months ago 8
SQL Question

Oracle SQL - select parts of a string

How can I select

abcdef.txt
from the following string?

abcdef.123.txt


I only know how to select abcdef by doing
select substr('abcdef.123.txt',1,6) from dual;

Answer

You can using || for concat and substr -3 for right part

select substr('abcdef.123.txt',1,6)  || '.' ||substr('abcdef.123.txt',-3) from dual;

or avoiding a concat (like suggested by Luc M)

select substr('abcdef.123.txt',1,7)   || substr('abcdef.123.txt',-3) from dual;