Table of ContentsPreviousNext

Ispirer             Ispirer


Ispirer SQLWays Database Migration Software

Returning Position of Substring in String

This subsection describes functions that return the position of a substring in the specified string in various databases and their conversion by SQLWays.

TABLE 62. Returning Position of Substring in String
Database
Syntax
Description
Microsoft SQL Server
CHARINDEX (substring, string [,start_location ] )
The CHARINDEX function returns the starting position of a substring in the specified character string.
Substring is an expression containing the sequence of characters to be found.
String is a character string or expression searched for the specified sequence.
Start_location is the character position to start searching for the substring. If start_location is not given, negative or zero, the search starts at the beginning of the string.
If either of expressions is NULL, CHARINDEX returns NULL when the database compatibility level is 7.0 or later. If the database compatibility level is 6.5 or earlier, CHARINDEX returns NULL only when both expressions are NULL.
If substring is not found within the string, CHARINDEX returns 0.
Return type: int
 
Example:
Returns the position of point in the title variable
CHARINDEX(`.', @title)
Oracle
INSTR ( string, substring
[,start_location [,occurrence] ] )
The INSTR function returns the starting position of a substring in the specified character string.
INSTR calculates strings using characters as defined by the input character set.
String is an expression containing character string, searched for the specified sequence.
Substring is an expression containing the sequence of characters to be found.
start_location is a nonzero integer indicating the character of string to start searching for substring. If position is negative, then INSTR counts and searches backward from the end of string. The default value is 1.
occurrence is an integer indicating which occurrence of string INSTR should search for. The value of occurrence must be positive. The default value is 1. If substring does not appear occurrence times after the position character of string, then the return value is 0.
Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
If either of expressions is NULL, INSTR returns NULL
Return type: NUMBER.
Example:
The following sample returns 7 - position of first comma in the constant string.
INSTR(`Austin,Boston,Cardiff', `,')
Sybase Adaptive Server Anyware
LOCATE (string, substring
[,start_location])
Returns the starting position of a substring in the specified character string.
 
string is the string to be searched.
 
substring is the string to be searched for. The maximum length is the 255 bytes.
 
start_location is the character position to start searching for the substring. The first character is position 1. If start_location is negative, the LOCATE function returns the last matching string offset rather than the first. A negative offset indicates how much of the end of the string is to be excluded from the search.
 
� If start_location is specified, the search starts at that offset into the string.
� If string is given as substring, the function returns a NULL value.
� If string is not found, 0 is returned. Searching for a zero-length string will return 1.
� If any of the arguments are NULL, the result is NULL.
MySQL
INSTR(string, substring)
Returns the starting position of a substring (substring) in the specified character string (string).
 
INSTR is an equivalent of the LOCATE function with two arguments (when start_location is ommitted).
LOCATE(substring, string
[,start_location])
Returns the starting position of a substring (substring) in the specified character string (string).
 
If start_location is specified then LOCATE returns the position of a substring (substring) in string (string), starting at position start_location.
 
� If substring is not in string then LOCATE returns 0.


Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.