InStr() and InstrRev() Function in MS Access

1. InStr() Function :
The InStr() Function returns the position of a string in another string. It always returns the first occurrence of the string. It works not case sensitive. It returns 0 if the string2 is not found in string1 or string1 is null or the parameter starts in the function is greater than length of string1 and it returns null if string1 is null and if the length of string2 is zero then it returns value of start parameter.

Syntax –

InStr(start, string1, string2, compare)

Parameters –

  • start : Optional (By default the position is 1)
  • String1 : Required (The string to be searched)
  • string2 : Required (The string to search for)
  • compare : Optional (Type of string comparison)

Possible Values –

  • -1 : Use the setting of Option Compare.
  • 0 : Binary comparison.
  • 1 : Textual comparison.
  • 2 : Comparison based on information in your database.

Return – It returns 0, 1 or null.



Example –

SELECT InStr("geeksforgeeks", "f") 
AS MatchPosition;

Output –

MatchPosition
6

Example –

SELECT InStr("DSA self paced", "a") 
AS MatchPosition;

Output –

MatchPosition
3



2. InstrRev() Function :
The InstrRev() Function function works like Instr() function but it returns position of the first occurrence of a string in another, from the end of string. The start parameter in this default -1.

Syntax :

InstrRev(string1, string2, start, compare)

Example –

SELECT InStrRev("geeksforgeeks", "k") 
AS MatchPosition;

Output –

MatchPosition
12

Example –

SELECT InStrRev("gfg", "k") 
AS MatchPosition;

Output –

MatchPosition
0
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.