Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Common Content for Data Engineering 10.5
  • All Products

Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

Back Next

INSTR

INSTR

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

Syntax

INSTR( 
string
, 
search_value
 [,
start
 [,
occurrence
 [,
comparison_type
 ]]] )
The following table describes the arguments for this command:
Argument
Required/ Optional
Description
string
Required
The 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_value
Required
Any 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'.
start
Optional
Must 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.
occurrence
Optional
A 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
Data Integration Service
 rounds it to the nearest integer value. If you pass a negative integer or 0, the session fails.
comparison_type
Optional
The string comparison type, either linguistic or binary, when the
Data Integration Service
 runs in Unicode mode. When the
Data Integration Service
 runs in ASCII mode, the comparison type is always 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.
Must be an integer value, either 0 or 1:
  • 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_value
, counting from left to right.
0 if the search is unsuccessful.
NULL if a value passed to the function is NULL.

Examples

The following expression returns the position of the first occurrence of the letter ‘a’, starting at the beginning of each company name. Because the
search_value
 argument is case sensitive, it skips the ‘A’ in ‘Blue Fin Aqua Center’, and returns the position for the ‘a’ in ‘Aqua’: 
INSTR( COMPANY, 'a' )
COMPANY
RETURN VALUE
 
Blue Fin Aqua Center
13
Maco Shark Shop
2
Scuba Gear
5
Frank's Dive Shop 
3
VIP Diving Club
0
The following expression returns the position of the second occurrence of the letter ‘a’, starting at the beginning of each company name. Because the
search_value
 argument is case sensitive, it skips the ‘A’ in ‘Blue Fin Aqua Center’, and returns 0: 
INSTR( COMPANY, 'a', 1, 2 )
COMPANY
RETURN VALUE
 
Blue Fin Aqua Center
0
Maco Shark Shop
8
Scuba Gear
9
Frank's Dive Shop 
0
VIP Diving Club
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. Because the
search_value
 argument is case sensitive, it skips the ‘A’ in 'Blue Fin Aqua Center’, and returns 0: 
INSTR( COMPANY, 'a', -1, 2 )
COMPANY
RETURN VALUE
 
Blue Fin Aqua Center
0
Maco Shark Shop
2
Scuba Gear
5
Frank's Dive Shop
0
VIP Diving Club
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): 
INSTR( COMPANY, 'Blue Fin Aqua Center', -1, 1 )
COMPANY
RETURN VALUE
 
Blue Fin Aqua Center
1
Maco Shark Shop
0
Scuba Gear
0
Frank's Dive Shop 
0
VIP Diving Club
0

Using Nested INSTR

You can nest the INSTR function within other functions to accomplish more complex tasks.
The following expression evaluates a string, starting from the end of the string. The expression finds the last (rightmost) space in the string and then returns all characters to the left of it: 
SUBSTR( CUST_NAME,1,INSTR( CUST_NAME,' ' ,-1,1 ))
CUST_NAME
RETURN VALUE
 
PATRICIA JONES
PATRICIA
MARY ELLEN SHAH
MARY ELLEN
The following expression removes the character '#' from a string: 
SUBSTR( CUST_ID, 1, INSTR(CUST_ID, '#')-1 ) || SUBSTR( CUST_ID, INSTR(CUST_ID, '#')+1 )
CUST_ID
RETURN VALUE
 
ID#33
ID33
#A3577
A3577
SS #712403399
SS 712403399
0 COMMENTS
We’d like to hear from you!