Taskflows

Back Next

instr

Returns the position of a character set in a string, counting from left to right.

Syntax

sff:instr(str, search, start, occurrence, comparison_type)

The following table describes the arguments:

Argument	Required/Optional	Description
str	Required	Passes the value that you want to evaluate. The string must be a character string. You can enter any valid transformation expression. The results of the expression must be a character string. Otherwise, instr converts the value to a string before evaluating it.
search	Required	The set of characters that you want to search for. You can enter any valid transformation expression. If you want to search for a character string, enclose the characters that you want to search for within single or double quotation marks. The value must match a part of the string. For example, if you use instr('Alfred Pope', 'Alfred Smith') the function returns 0. The value is case sensitive.
start	Optional	The position in the string where you want to start the search. You can enter any valid transformation expression. The value must be an integer. The default is 1, meaning that instr starts the search at the first character in the string. If the start position is 0, instr searches from the first character in the string. If the start position is a positive number, instr locates the start position by counting from the beginning of the string. If the start position is a negative number, instr locates the start position by counting from the end of the string. If you omit this argument, the function uses the default value of 1.
occurrence	Optional	You can enter any valid transformation expression. If the search value appears more than once in the string, you can specify which occurrence you want to search for. For example, you would enter 2 to search for the second occurrence from the start position. You can enter a positive integer that is greater than 0. If you omit this argument, the function uses the default value of 1, which means that instr searches for the first occurrence of the search value. If you pass a decimal value, the function rounds it to the nearest integer value. If you pass a negative integer or 0, the function is not valid.
comparison_type	Optional	The string comparison type, either linguistic or binary. Linguistic comparisons take language-specific collation rules into account, while binary comparisons perform bitwise matching. For example, the German sharp s character matches the string “ss” in a linguistic comparison, but not in a binary comparison. Binary comparisons run faster than linguistic comparisons. You must enter one of the following integer values: 0: instr performs a linguistic string comparison. 1: instr performs a binary string comparison. Default is 0.

Return Value

Integer if the search is successful. Integer represents the position of the first character in the search
argument, counting from left to right.

0 if the search is unsuccessful.

NULL if a value passed to instr is NULL.

Examples

The following expression returns the position of the first occurrence of the letter ‘a’, starting from the beginning of each company name:

sff:instr( COMPANY, 'a' )

The following table lists some sample values and return values:

COMPANY	RETURN VALUE
Blue Fin Aqua Center	13
Maco Shark Shop	2
Scuba Gear	5
Frank's Dive Shop	3
VIP Diving Club	0

Because the search
argument is case sensitive, it skips the ‘A’ in ‘Blue Fin Aqua Center’, and returns the position for the ‘a’ in ‘Aqua’.

The following expression returns the position of the second occurrence of the letter ‘a’, starting from the beginning of each company name:

sff:instr( COMPANY, 'a', 1, 2 )

The following table lists some sample values and return values:

COMPANY	RETURN VALUE
Blue Fin Aqua Center	0
Maco Shark Shop	8
Scuba Gear	9
Frank's Dive Shop	0
VIP Diving Club	0

Because the search
argument is case sensitive, it skips the ‘A’ in ‘Blue Fin Aqua Center’, and returns 0.

The following expression returns the position of the second occurrence of the letter ‘a’ in each company name, starting from the last character in the company name.

sff:instr( COMPANY, 'a', -1, 2 )

The following table lists some sample values and return values:

COMPANY	RETURN VALUE
Blue Fin Aqua Center	0
Maco Shark Shop	2
Scuba Gear	5
Frank's Dive Shop	0
VIP Diving Club	0

Because the search
argument is case sensitive, it skips the ‘A’ in 'Blue Fin Aqua Center’, and returns 0.

The following expression returns the position of the first character in the string ‘Blue Fin Aqua Center’, starting from the last character in the company name:

sff:instr( COMPANY, 'Blue Fin Aqua Center', -1, 1 )

The following table lists some sample values and return values:

COMPANY	RETURN VALUE
Blue Fin Aqua Center	1
Maco Shark Shop	0
Scuba Gear	0
Frank's Dive Shop	0
VIP Diving Club	0

Rename Saved Search

Table of Contents

Taskflows

Taskflows

instr

instr

Syntax

Return Value

Examples