CODEWITHSUNDEEP
sqloracle-databasesql-view
Arpit Mishra
Arpit Mishra

Reputation: 90

How to use a fetched value in INSTR in Oracle PLSQL

I am trying to create a view which combines two tables A and B to fetch data. The main function of this view is to decode the value which A has in one of its column

Name~LastName~DOB~placeofBirth

As we can see the string is separated by ~ The Special character ~ is in the table B but also encoded.

&~*+

My task is to fetch the second character from Table B and then use this for separating values in A as individual values. Since we cant declare variables in Views where can i store ~ charecter to compare it to values

Below is sample of what im trying to do which is giving me error

SELECT 
  Substr(B.SpecialCharecterss,2,1) Delimiter,  

  SUBSTR(A.CodedString,1, INSTR(A.CodedString, Delimiter, 1, 1) -1) , //Throws error here 

 // Similarly other fields

Upvotes: 1

Views: 434

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

One option is to use a common table expression:

WITH cteDelimiter AS (SELECT  Substr(B.SpecialCharecterss,2,1) AS Delimiter 
                        FROM B)
SELECT SUBSTR(A.CodedString, 1, INSTR(A.CodedString, d.Delimiter, 1, 1) - 1)
  FROM A
  CROSS JOIN cteDelimiter d

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You could do this using regexp_substr():

select regexp_substr(coded_string,
                     replace('[^X]+', 'X', substr(chars, 2, 1)),
                     1, 2)
from (select 'Name~LastName~DOB~placeofBirth' as coded_string, '&~*+' as chars from dual) b

The last argument is which of the fields you want in coded_string.

Upvotes: 1

Related Questions