{"id":19210,"date":"2021-08-06T08:00:08","date_gmt":"2021-08-05T22:00:08","guid":{"rendered":"https:\/\/database.guide\/?p=19210"},"modified":"2021-08-06T08:00:08","modified_gmt":"2021-08-05T22:00:08","slug":"instr-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/instr-function-in-oracle\/","title":{"rendered":"INSTR() Function in Oracle"},"content":{"rendered":"\n

In Oracle, the INSTR()<\/code> function searches for a substring in a given string, and returns an integer indicating the position of the first character of this substring. If the substring isn’t found, the function returns 0<\/code>.<\/p>\n\n\n\n

INSTR()<\/code> requires at least two arguments; the string, and the substring. It also accepts an optional third and fourth arguments that allows you to specify the starting position to search, and which occurrence to search for.<\/p>\n\n\n\n

INSTR()<\/code> can also be thought of as a group of functions. There are five separate functions; INSTR()<\/code>, INSTRB()<\/code>, INSTRC()<\/code>, INSTR2()<\/code>, and INSTR4()<\/code>. Each function calculates the length in a different way.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

{ INSTR\n| INSTRB\n| INSTRC\n| INSTR2\n| INSTR4\n}\n(string , substring [, position [, occurrence ] ])<\/code><\/pre>\n\n\n\n
Where string<\/code> is the string to search, substring<\/code> is the substring to find, position<\/code> is the starting position of the substring, and occurrence<\/code> is which occurrence to find.<\/p>\n\n\n\n
The functions calculate lengths as follows:<\/p>\n\n\n\n
Function<\/th>Calculates length using…<\/th><\/tr><\/thead>
INSTR()<\/code><\/td>Characters as defined by the input character set, with the first character of string having position 1.<\/td><\/tr>
INSTRB()<\/code><\/td>Bytes<\/td><\/tr>
INSTRC()<\/code><\/td>Unicode complete characters<\/td><\/tr>
INSTR2()<\/code><\/td>UCS2 code points<\/td><\/tr>
INSTR4()<\/code><\/td>UCS4 code points<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a basic example:<\/p>\n\n\n\n
SELECT INSTR('Big fat cat', 'fat')\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
5<\/pre>\n\n\n\n
Compared with INSTRB()<\/code><\/h2>\n\n\n\n
This example shows how the results can differ, depending on which specific function you’re using, and the character set involved. <\/p>\n\n\n\n
In this case, we compare INSTR()<\/code> with INSTRB()<\/code>:<\/p>\n\n\n\n
SELECT \n    INSTR('B\u00f6y\u00fck ya\u011fl\u0131 pi\u015fik', 'ya\u011fl\u0131') AS INSTR,\n    INSTRB('B\u00f6y\u00fck ya\u011fl\u0131 pi\u015fik', 'ya\u011fl\u0131') AS INSTRB\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   INSTR    INSTRB \n________ _________ \n       7         9<\/pre>\n\n\n\n
We can see that the two functions returned two different results. This is because some characters in this string use two bytes. <\/p>\n\n\n\n
The INSTR()<\/code> function returns the position as defined by the input character set, whereas the INSTRB()<\/code> function returns the position based in bytes<\/em>.<\/p>\n\n\n\n
If we return to the original string, the results are the same between the two functions:<\/p>\n\n\n\n
SELECT \n    INSTR('Big fat cat', 'fat') AS INSTR,\n    INSTRB('Big fat cat', 'fat') AS INSTRB\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   INSTR    INSTRB \n________ _________ \n       5         5 <\/pre>\n\n\n\n
That’s because this string uses just one byte per character, and so the length in bytes is the same as the number of characters.<\/p>\n\n\n\n
Starting Position<\/h2>\n\n\n\n
Here’s an example that specifies the position for which to start the search:<\/p>\n\n\n\n
SELECT INSTR('That fat cat', 'at', 8)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
11<\/pre>\n\n\n\n
In this case, the search starts at position 8, which is after the first two occurrences. Therefore, we get the position of the third match.<\/p>\n\n\n\n
Specify Which Occurrence<\/h2>\n\n\n\n
Here’s an example of specifying which occurrence to find:<\/p>\n\n\n\n
SELECT INSTR('That fat cat', 'at', 1, 2)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
7<\/pre>\n\n\n\n
In this case, we started at position 1, and then searched for the second occurrence from that starting position.<\/p>\n\n\n\n
Here it is again, but this time we compare three different values for the occurrence<\/code><\/em> argument:<\/p>\n\n\n\n
SELECT \n    INSTR('That fat cat', 'at', 1, 1) AS \"o1\",\n    INSTR('That fat cat', 'at', 1, 2) AS \"o2\",\n    INSTR('That fat cat', 'at', 1, 3) AS \"o3\"\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   o1    o2    o3 \n_____ _____ _____ \n    3     7    11<\/pre>\n\n\n\n
But here’s what happens if we increase the position<\/em><\/code> argument:<\/p>\n\n\n\n
SELECT \n    INSTR('That fat cat', 'at', 5, 1) AS \"o1\",\n    INSTR('That fat cat', 'at', 5, 2) AS \"o2\",\n    INSTR('That fat cat', 'at', 5, 3) AS \"o3\"\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
   o1    o2    o3 \n_____ _____ _____ \n    7    11     0 <\/pre>\n\n\n\n
In this case we don’t get the position of the first occurrence, because it’s located before our starting position. We also get 0<\/code> in the third column because there’s no third occurrence, based on our starting position.<\/p>\n\n\n\n
Negative Position<\/h2>\n\n\n\n
Specifying a negative value for the position causes the starting position to be counted backwards from the end of the string, and for Oracle to search backward from that position:<\/p>\n\n\n\n
SELECT INSTR('That fat cat', 'at', -3)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
7<\/pre>\n\n\n\n
And any occurrence that is specified is counted backward from that position:<\/p>\n\n\n\n
SELECT INSTR('That fat cat', 'at', -3, 2)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
3<\/pre>\n\n\n\n
Null Arguments<\/h2>\n\n\n\n
If any (or all) of the arguments are null<\/code>, the result is null<\/code>:<\/p>\n\n\n\n
SET NULL 'null';\nSELECT \n    INSTR(null, 'f', 1, 1) AS r1,\n    INSTR('Coffee', null, 1, 1) AS r2,\n    INSTR('Coffee', 'f', null, 1) AS r3,\n    INSTR('Coffee', 'f', 1, null) AS r4\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
     R1      R2      R3      R4 \n_______ _______ _______ _______ \n   null    null    null    null<\/pre>\n\n\n\n
By default, SQLcl and SQL*Plus return a blank space whenever null<\/code> occurs as a result of a SQL SELECT<\/a><\/code> statement. <\/p>\n\n\n\n
However, you can use SET NULL<\/a><\/code> to specify a different string to be returned. Here I specified that the string null<\/code> should be returned.<\/p>\n\n\n\n
Incorrect Argument Count<\/h2>\n\n\n\n
Calling INSTR()<\/code> without passing any arguments results in an error:<\/p>\n\n\n\n
SELECT INSTR()\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Error starting at line : 1 in command -\nSELECT INSTR()\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00938: not enough arguments for function\n00938. 00000 -  \"not enough arguments for function\"\n*Cause:    \n*Action:<\/pre>\n\n\n\n
And passing too many arguments also results in an error:<\/p>\n\n\n\n
SELECT INSTR('Big fat cat', 'at', 1, 2, 3)\nFROM DUAL;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Error starting at line : 1 in command -\nSELECT INSTR('Big fat cat', 'at', 1, 2, 3)\nFROM DUAL\nError at Command Line : 1 Column : 38\nError report -\nSQL Error: ORA-00939: too many arguments for function\n00939. 00000 -  \"too many arguments for function\"\n*Cause:    \n*Action:<\/pre>\n","protected":false},"excerpt":{"rendered":"
In Oracle, the INSTR() function searches for a substring in a given string, and returns an integer indicating the position of the first character of this substring. If the substring isn’t found, the function returns 0. INSTR() requires at least two arguments; the string, and the substring. It also accepts an optional third and fourth … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[93,67,20],"class_list":["post-19210","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-functions","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19210","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=19210"}],"version-history":[{"count":8,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19210\/revisions"}],"predecessor-version":[{"id":19220,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19210\/revisions\/19220"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=19210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=19210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=19210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}