CODEWITHSUNDEEP
sqloracle-database
RealMan
RealMan

Reputation: 129

How to slice a string using INSTR()

I need to take the value inside the brackets ( [ ] ): Example: 

[TextValue][WINDOM][Camry]

My code: 

 select substr('[TextValue][WINDOM][Camry]',  
           instr('[TextValue][WINDOM][Camry]', '[',1,2), 
           instr('[TextValue][WINDOM][Camry]', ']',1,2) )                            
      from dual

desired result: WINDOM

Upvotes: 0

Views: 54

Answers (2)

MT0
MT0

Reputation: 167962

You are almost there but SUBSTR has the signature SUBSTR( string, start, length ):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test_data ( value ) AS
  SELECT '[TextValue][WINDOM][Camry]' FROM DUAL;

Query 1:

SELECT SUBSTR(
         value,
         open_bracket2 + 1,
         close_bracket2 - open_bracket2 - 1
       ) AS bracket2
FROM   (
  SELECT value,
         INSTR( value, '[', 1, 2 ) AS open_bracket2,
         INSTR( value, ']', 1, 2 ) AS close_bracket2
  FROM   test_data
)

Results:

| BRACKET2 |
|----------|
|   WINDOM |

Upvotes: 1

jurden
jurden

Reputation: 91

Third parameter is length:

with s as (select '[TextValue][WINDOM][Camry]' as Text from Dual)  

select substr(substr(s.Text, instr(s.Text, '[', 1, 2) + 1), 1, instr(substr(s.Text, instr(s.Text, '[', 1, 2) + 1), ']') - 1)                           
from s

Upvotes: 2

Related Questions