CODEWITHSUNDEEP
sqloracle-database
Amber
Amber

Reputation: 944

Oracle/ PLSQL- Substring Till Next Occurence of Character

I have a string

myString = "C=3;A=1;B=2;"

from which I'm trying to extract the value of A as follows

SELECT
  SUBSTR(myString,
         INSTR(myString, 'A='),
         INSTR(myString, ';')-1
        ) AS val_a
FROM DUAL;

which gives me the following result

+-------+
| Val_A | 
+-------+
|  A=1  |
+-------+

But I just want the value of A. So I tried to add 2 to the second parameter of SUBSTR

SUBSTR(myString,
       INSTR(myString, 'A=')+2,
       INSTR(myString, ';')-1
      )

but that gave me

+-------+
| Val_A | 
+-------+
|  1;B  |
+-------+

Could someone please tell me how to get just the part between A= and the next ;?

Upvotes: 1

Views: 232

Answers (2)

MT0
MT0

Reputation: 167972

Without regular expressions:

WITH data ( mystring ) AS (
  SELECT 'C=3;AA=1;A=4;B=2;' FROM DUAL
)
SELECT SUBSTR( mystring, val_a_start, val_a_end - val_a_start ) AS val_a
FROM   (
  SELECT mystring,
         INSTR( mystring, ';A=' ) + 3 AS val_a_start,
         INSTR( mystring, ';', INSTR( mystring, ';A=' ) + 3 ) AS val_a_end
  FROM   (
   SELECT ';' || mystring || ';' AS mystring
   FROM   data
  )
)

With regular expressions:

WITH data ( mystring ) AS (
  SELECT 'C=3;AA=1;A=4;B=2;' FROM DUAL
)
SELECT REGEXP_SUBSTR( ';' || mystring || ';', ';A=(.*?);', 1, 1, NULL, 1 ) AS val_a
FROM   data

Upvotes: 1

user330315
user330315

Reputation:

This can be done using a regular expression and regexp_substr()

with data (mystring) as (
   select 'C=3;A=;B=2;' from dual union all
   select 'C=3;A=1;B=2;' from dual union all
   select 'C=3;A=12;B=2;' from dual union all
   select 'C=3;A=123;B=2;' from dual
)
select mystring, regexp_substr(mystring, '(A=)([^\;)]*)', 1, 1, 'i', 2) as aval
from data;

(The with data (...) part is only there to generate some testdata)

returns 

MYSTRING       | AVAL
---------------+-----
C=3;A=;B=2;    |     
C=3;A=1;B=2;   | 1   
C=3;A=12;B=2;  | 12  
C=3;A=123;B=2; | 123

Upvotes: 2

Related Questions