Search For

INSTR - Search for a String

Flag Hide Line Nos
Flagging a Snippet, takes the snippet to Moderator Attention;


0
INSTR function searches for pattern returns Position of Match if successful and 0 if the match is unsuccessful.

INSTR function is similar to SQL LIKE Function

Syntax

INSTR( string, search_value [,start [,occurrence [,comparison_type ]]] )

Example : Check the string contains the keyword 'Inform'
    INSTR(port,'Inform')

portoutput
Informatica1
An Information4
Hello0

ArgumentRequired/OptionalDescription
stringRequiredThe string must be a character string. Passes the value you want to evaluate.You can enter any valid transformation expression. The results of the expression must be a character string. If not, INSTR converts the value to a string before evaluating it.
search_valueRequiredAny value. The search value is case sensitive. The set of characters you want to search for. The search_value must match a part of the string. For example, if you write INSTR('Alfred Pope', 'Alfred Smith') the function returns 0. You can enter any valid transformation expression. If you want to search for a character string, enclose the characters you want to search for in single quotation marks, for example 'abc'.
startOptionalMust be an integer value. The position in the string where you want to start the search. You can enter any valid transformation expression. 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.
occurrenceOptionalA positive integer greater than 0. 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. If you omit this argument, the function uses the default value of 1, meaning that INSTR searches for the first occurrence of the search value. If you pass a decimal, the Integration Service rounds it to the nearest integer value. If you pass a negative integer or 0, the session fails.
comparison_typeOptionalThe string comparison type, either linguistic or binary, when the Integration Service runs in Unicode mode. When the integration Service runs in ASCII mode, the comparison type is always binary.




Vote! You feel this code is working like charm ? Go a head Login and Press up arrow.
Didn't work ?, press down arrow and let us know the error in comments

Comments