Tuesday, January 29, 2013

Microsoft Access - InStr Function

InStr Function 

This function will return the first position of one string within another. This function will start searching the string from left hand side of the string.
Syntax:

Instr(start,String_being_searched,String_to_be_searched,compare) or
Instr(start,String_being_searched,[ColumnName],compare) or
Instr(start,[ColumnName], String_to_be_searched,compare) or
Instr(start,[ColumnName1],[ColumnName2],compare)


This function has the following arguments.

Start is an integer value it instructs Instr function where to start the search string1 within string2. This is optional. If mentioned you have to supply the compare argument.
 
The String expression being searched is specified by String_being_searched argument.
 
The String that has to be sought is specified by String_to_be_searched. argument.
 
The compare argument is an integer indicating what kind of comparison to use when evaluating substrings. This is optional. The setting for this argument is as follows.


Value Description
-1 Performs a comparison using the setting from option compare statement.
0 Binary comparison
1 Textual comparison


If string_being_searched is 0, Instr function returns 0.
If String_being_searched is Null, Instr Function returns Null.
If String_to_be_searched is a zero-length string “” then Instr function will return start argument value.
If String_to_be_searched is Null, Instr Function returns Null.
If String_to_be_searched is not found within String_being_searched, Instr Function returns 0.
If String_to_be_searched is found within String_being_searched, Instr Function returns position at which the match was found.
If start is greater than the length of the String_to_be_searched, Instr function will return 0.

 

Examples:

InStr("Hello World","lo") returns 4. It searches “lo” in the string “Hello World” and match is found at the position 4.
InStr(3,"Hello World","lo",1) returns 4. It searches “lo” in the string “Hello World” from the position 3 as specified in the start argument and match is found at the position 4 and uses a textual comparison.



 

 

 

Note: This function is used to extract the first word and last word in a string.
Syntax: Left(String,Instr(String,” “)-1) will extract first word in a string.

StrReverse(Left(StrReverse(String),InStr(StrReverse("String")," ")-1)) will extract the last word in a string.

Example:

Left(“Hello World”,Instr(“Hello World”,” “)-1) will return “Hello”.





 StrReverse(Left(StrReverse("The moon is bright and white"),InStr(StrReverse("The moon is bright and white")," ")-1)) will return white.

 

 

No comments:

Post a Comment