CODEWITHSUNDEEP
sqloracle-database
mahta 1988
mahta 1988

Reputation: 65

how to remove characters from beginning and end of a column in sql

I have a column in sql which shows result like this "T/PROD/Logs/somename.log" what I want to do is only extract "somename" part from that string. what I tried is

SUBSTR(A.SESSION_LOG_FILE, 41, LENGTH(A.SESSION_LOG_FILE) - 4)

but only removes the character from the beginning of the string

Upvotes: 0

Views: 517

Answers (3)

Littlefoot
Littlefoot

Reputation: 142713

Using such an approach, you'd select substring that begins after the last slash / characters and ends before the next non-word (in this case, a dot . character):

SQL> with test (col) as
  2    (select 'T/PROD/Logs/somename.log' from dual)
  3  select
  4    regexp_substr(substr(col, instr(col, '/', -1) + 1), '\w+') result
  5  from test
  6  /

RESULT
--------
somename

SQL>

Upvotes: 1

user14095651
user14095651

Reputation:

In Tsql, if "T/PROD/Logs/" never changes, I would try to do this:

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING('T/PROD/Logs/somename.log', 13, LEN('T/PROD/Logs/somename.log'))),5,LEN('T/PROD/Logs/somename.log')))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want between the last slash and the period, then one method is regexp_replace():

select regexp_replace('T/PROD/Logs/somename.log', '.*/([^/.]*).log$', '\1')

Upvotes: 1

Related Questions