CODEWITHSUNDEEP
textsubstr
leroy
leroy

Reputation: 31

How to get text from between characters in oracle sql

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

Upvotes: 3

Views: 37137

Answers (2)

Neeraj B.
Neeraj B.

Reputation: 642

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;

Upvotes: 6

jalal rasooly
jalal rasooly

Reputation: 705

I have not checked yet, but i think this would work for you

substr(var1,1,instr(site.description,'-',1,1)+1-instr(site.description,'-',1,2)-1)

it should work,if it did not just need a little change.let me know the result

Upvotes: 0

Related Questions