CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Austin
Austin

Reputation: 2265

INSTR() Returning Same Value For Different Occurrences

I have a column storing all responses to a series of questions, all within the single column responses. I cannot adjust this column into separate columns nor adjust how this column is being stored, only manipulate the column in my query. Here is a CTE with example data and the query I'm trying to use to determine the start of each question:

with t as (
select 'Do you have a cell phone with data plan: No
Do you have a cell phone with wifi capability: No
Do you have home internet access: Yes
Do you have public internet access: No' responses
from dual

union all

select 'Do you have a cell phone with data plan: Yes
Do you have a cell phone with wifi capability: Yes
Do you have home internet access: Yes
Do you have public internet access: Yes'
from dual
)
select instr(responses, 'Do', 1) o1
    , instr(responses, 'Do', 2) o2
    , instr(responses, 'Do', 3) o3
    , instr(responses, 'Do', 4) o4
from t

Results:

o1 o2 o3 o4
1 45 45 45
1 46 46 46

When running this query, I can correctly get the first and second occurrence of 'Do' via instr, but occurrences 3 and 4 both return the same value as occurrence 2. My assumption is that I'm using instr incorrectly, but I'm not sure how to correctly format this to get occurrences 3 and 4.

Upvotes: 0

Views: 283

Answers (1)

ch_g
ch_g

Reputation: 1440

The third parameter is "position" from which to search, not occurrence. see here - https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions068.htm

You sql can be modified to -

select instr(responses, 'Do', 1, 1) o1
    , instr(responses, 'Do', 1, 2) o2
    , instr(responses, 'Do', 1, 3) o3
    , instr(responses, 'Do', 1, 4) o4
from t

Upvotes: 1

Related Questions