Arguments expression1 Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category. expression2 Is an expression, usually a column that is searched for the specified sequence. expression2 is of the character string data type category. start_location Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2. Return Types intRes If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.
CHARINDEX (T-SQL) Returns the starting position of the specified expression in a character string. Syntax CHARINDEX(expression1, expression2 [, start_location])Arguments expression1 Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category. expression2 Is an expression, usually a column that is searched for the specified sequence. expression2 is of the character string data type category. start_location Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2. Return Types intRes If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, CHARINDEX returns NULL only when both expression1 and expression2 are NULL. Examples The first code example returns the position at which the sequence wonderful begins in the notes column of the titles table. The second example uses the optional start_location parameter to begin looking for wonderful in the fifth character of the notes column.USE pubsGOSELECT CHARINDEX('wonderful', notes)FROM titlesWHERE title_id = 'TC3218'GO -- Use the optional start_location parameter to start searching -- for wonderful starting with the fifth character in the notes-- column.USE pubsGOSELECT CHARINDEX('wonderful', notes, 5)FROM titlesWHERE title_id = 'TC3218'GO Here is the result set for either query:----------- 47 (1 row(s) affected) See Also + (String Concatenation) String Functions (c) 1988-98 Microsoft Corporation. All Rights Reserved.
expression1
Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.
expression2
Is an expression, usually a column that is searched for the specified sequence. expression2 is of the character string data type category.
start_location
Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.
Return Types
intRes
If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.
Returns the starting position of the specified expression in a character string. Syntax
CHARINDEX(expression1, expression2 [, start_location])Arguments
expression1
Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.
expression2
Is an expression, usually a column that is searched for the specified sequence. expression2 is of the character string data type category.
start_location
Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.
Return Types
intRes
If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, CHARINDEX returns NULL only when both expression1 and expression2 are NULL. Examples
The first code example returns the position at which the sequence wonderful begins in the notes column of the titles table. The second example uses the optional start_location parameter to begin looking for wonderful in the fifth character of the notes column.USE pubsGOSELECT CHARINDEX('wonderful', notes)FROM titlesWHERE title_id = 'TC3218'GO -- Use the optional start_location parameter to start searching -- for wonderful starting with the fifth character in the notes-- column.USE pubsGOSELECT CHARINDEX('wonderful', notes, 5)FROM titlesWHERE title_id = 'TC3218'GO Here is the result set for either query:----------- 47 (1 row(s) affected) See Also
+ (String Concatenation) String Functions
(c) 1988-98 Microsoft Corporation. All Rights Reserved.