how do i select character from left side excluding .txt at right side Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of how do i select character from left side excluding .txt at right side without wasting too much if your time.

The question is published on by Tutorial Guruji team.

The column have a not uniform data, some have “.txt” at the end. Consider one of the entries as “ABCDEFG.TXT” i want my select statement to return me “ABCDEFG”.

Answer

with demo (text) as
     ( select 'ABCDEFG.TXT' from dual union all
       select 'HIJKL' from dual )
select text
     , regexp_replace(text, '.TXT$')
from   demo;

TEXT         REGEXP_REPLACE(TEXT,'.TXT$')
------------ --------------------------------------------
ABCDEFG.TXT  ABCDEFG
HIJKL        HIJKL

Or if you need it to be case-insensitive:

with demo (text) as
     ( select 'ABCDEFG.TXT' from dual union all
       select 'abcdefg.txt' from dual union all
       select 'HIJKL' from dual )
select text
     , regexp_replace(text, '.TXT$', '', 1, 1, 'i') as replaced
from   demo;

TEXT         REPLACED
------------ --------------------------------------------
ABCDEFG.TXT  ABCDEFG
abcdefg.txt  abcdefg
HIJKL        HIJKL

Or to strip any three-character file extension:

regexp_replace(text, '....$', '', 1, 1, 'i')

Or to strip any extension of any length:

regexp_replace(text, '.[^.]*$', '', 1, 1, 'i')
We are here to answer your question about how do i select character from left side excluding .txt at right side - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji