Why isn't SUBSTR stopping at the end index using instr?

July 27, 2022

I have a string value I’d like to make more presentable:

'Course grade for <font color="#FF0000">Student Name</font>'

but my query to extract the Student Name:

SELECT SUBSTR(col, instr(col, '>',1,1)+1, instr(col, '<',1,2)) FROM my_table where course_id=1

returns Student Name</font> despite instr('<',1,2) returning the correct index.

I’m not sure why it doesn’t stop on that index and returns the rest of the string. Ideally I’d like it to return Student Name

Many thanks!

>Solution :

You have to subtract the start of the string, as the second paramter is length and INSTR counts from the start

CREATE TABLE my_table (course_id int,col varchar2(200))

INSERT INTO my_table VALUES(1,'Course grade for <font color="#FF0000">Student Name</font>')

INSERT INTO my_table VALUES(1,'Course grade for <font color="#FF0000">abel </font>')

SELECT SUBSTR(col, instr(col, '>',1,1)+1, instr(col, '</',1,1) -  instr(col, '>',1,1) - 1) FROM my_table where course_id=1

| SUBSTR(COL,INSTR(COL,'>',1,1)+1,INSTR(COL,'</',1,1)-INSTR(COL,'>',1,1)-1) |
| :------------------------------------------------------------------------ |
| Student Name                                                              |
| abel                                                                      |

db<>fiddle here