How to slice a string using INSTR()

Question

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

jurden · Accepted Answer · 2018-04-12 09:52:35Z

2

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

answered Apr 12, 2018 at 9:52

jurden

914 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2018-04-12 09:55:12Z

1

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 |

answered Apr 12, 2018 at 9:55

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

How to slice a string using INSTR()

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related