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

Article Tags :

SQL

DBMS-SQL