Looking for string using CHARINDEX-CodePudding

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.

CodePudding user response：

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;

SELECT title
FROM   (
  SELECT title
  FROM   Book
  WHERE  INSTR(title, 'Harry') > 0
  ORDER BY title
)
WHERE  ROWNUM < 100;