-1

I am required to use char index to locate a section of a word and to be the first 100 object it finds inside a database but for some reason, it is giving me several errors and have no clue what is wrong. I am trying to avoid using LIKE function.

''' SELECT CHARINDEX('Harry', TITLE ), TITLE FROM Book LIMIT 100;

''' It keeps saying it is an invalid identifier. I have looked at several sources but can't seem to figure out what is happening. Any help would be appreciated.

Calvin Lee
  • 33
  • 6

1 Answers1

1

CHARINDEX is not a valid Oracle function and LIMIT is not used in Oracle. You want the INSTR function.

From Oracle 12, you can use:

SELECT INSTR(title, 'Harry'),
       TITLE
FROM   Book
FETCH FIRST 100 ROWS ONLY;

or, in earlier versions (since you aren't using an ORDER BY clause and are just getting the first 100 random rows):

SELECT INSTR(title, 'Harry'),
       TITLE
FROM   Book
WHERE  ROWNUM < 100;

If you want to use it as a filter and use an ORDER BY clause then:

SELECT TITLE
FROM   Book
WHERE  INSTR(title, 'Harry') > 0
ORDER BY title
FETCH FIRST 100 ROWS ONLY;

or

SELECT title
FROM   (
  SELECT title
  FROM   Book
  WHERE  INSTR(title, 'Harry') > 0
  ORDER BY title
)
WHERE  ROWNUM < 100;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Not sure why but when using INSTR, my putout is different compared to when using Like function. ''' SELECT TITLE FROM Book WHERE Title LIKE '%Harry%' AND ROWNUM <= 100 ORDER BY TITLE; SELECT INSTR(TITLE, '%Harry%'), TITLE FROM Book WHERE ROWNUM <= 100 ORDER BY TITLE; ''' The output for the INSTR givers me the same values even i fi change the column. – Calvin Lee Oct 23 '21 at 19:40
  • @CalvinLee Don't use `ROWNUM` with `ORDER BY` in that way. Look at [this](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) for how to use it. And you don't need to use `%` with `INSTR`; just use `INSTR(title, 'Harry')`. – MT0 Oct 23 '21 at 19:47
  • So I tried following the format of the link you sent , but the INSTR still doesn't pick up the values and give me everything in the column. ''' SELECT INSTR(DESCRIPTION, 'Harry'), TITLE FROM Book ORDER BY TITLE OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY; ''' Does the INSTR function not sort out the values that exist in the description? – Calvin Lee Oct 23 '21 at 20:08
  • @CalvinLee Only when you use it in a `WHERE` condition (as I show in the answer) and filter the rows. – MT0 Oct 23 '21 at 20:24