The InStr function is used to locate a substring within a larger string. This function is useful for searching a character field to find the position of a specific character or group of characters. It can also be used to find any occurrence of the substring.
For example, to select all of the streets in the POINTS table that contain the word "Main" in the STREET column, set up your SQL Select statement as follows:
- Select Columns:
* - from Tables:
Points - where Condition:
InStr(1,STREET,"Main") > 0 - into Table Named:
Selection
In the above example, if the InStr function finds the word "Main" anywhere in the STREET column of the Points table, it will return a value greater than zero (the position of the first letter of "Main" in the string being searched). The syntax of the INSTR function is as follows:
INSTR (position, string, substring)
where:
- position is the starting position of the search
- string is the text string or field containing the string to be searched
- substring is the text string that you are searching for
To find the position of the word "test" in a given text string, the InStr function will look like this:
INSTR(1,"This is a test of the Instr function ","test")
When this statement is executed, the value 11 is returned.