How to get text from between characters in oracle sql

substr, text

Solution

Suppose your data resides in a tabled named test_n with only column val with above values as mentioned in your question, the query is:

select val
     , instr(val, '-', 1,1) + 1 START_POS
     , instr(val, '-',1,2) END_POS
     , substr(val, instr(val, '-', 1,1) + 1, decode(instr(val, '-',1,2),0,length(val)+1,instr(val, '-',1,2) ) - instr(val, '-', 1,1)-1 ) result
FROM test_n;

Problem

Hi this is my first post and I'm also new to sql. I am trying to extract text from within a string I have a table column that looks like this `site - abc - left` `site - def - left` `site - ghi - right - inner` `site - jkl - right - inner` `site - mno` `site - pqr` I need a query that would return text inbetween the first two '-' but as per example some only have one '-'. For example. `abc` `def` `ghi` `jkl` `mno` `pqr` Any help greatfully accepted The code I have been working with only gives me the first column 'site'. `SELECT SUBSTR(site.description,1,instr(site.description,'-',1,1)-1) AS loc` `FROM table`

Original source