LucidDbAppLib INSTR
From Eigenpedia
Contents |
Syntax
APPLIB.INSTR ( in_string, sub_string ) APPLIB.INSTR ( in_string, sub_string, start_position, nth_appearance )
Purpose
Returns the location of a substring in a string. The first position in a string is 1.
Parameters
- in_string [VARCHAR(65535)]: string to search
- sub_string [VARCHAR(65535)]: substring to search for in in_string
- start_position [INTEGER]: the position in in_string to start the search. This parameter is optional, if omitted defaults to 1, which is the beginning of the string. If negative, function counts back start_position number of characters from the end of in_string and then searches towards the beginning of in_string.
- nth_appearance [INTEGER]: which number of appearance to search for. This parameter is optional, if omitted, defaults to 1.
Output
- [INTEGER] - the beginning position of the sub_string within the in_string (or 0 if not found), returns null on null input
- Exception for the following reasons
- start position is 0
- nth appearance is less than 1
- start position is greater than max length of in_string
- in_string or substring are over 65535 characters
Example
| Example | Result |
|---|---|
| INSTR('This is a test isn''t it?', 'is') | 3 |
| INSTR('This is a test isn''t it?', 'is', 4, 2) | 16 |
| INSTR('This is a test isn''t it?, 'is', -2, 1) | 16 |
Source Code
http://p4web.eigenbase.org/open/dev/luciddb/src/com/lucidera/luciddb/applib/string/InStrUdf.java
Return to LucidDbAppLib page
