Searches an expression for
another expression and returns its starting position if found.
CHARINDEX ( expressionToFind ,expressionToSearch [,start_location ] )
expressionToFind
Is a character expression that
contains the sequence to be found.
ExpressionToFind is limited to 8000 characters.
expressionToSearch
Is a character expression to be searched.
start_location
Is an integer or bigint expression at
which the search starts.
If start_location is not specified, is a
negative number, or is 0, the search starts at the beginning of expressionToSearch.
bigint if expressionToSearch is of the varchar(max), nvarchar(max), or varbinary(max) data types; otherwise, int.
If either expressionToFind or expressionToSearch is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.
CHARINDEX cannot be used with text, ntext,
and image data types.
If either expressionToFind or expressionToSearch is
NULL, CHARINDEX returns NULL.
If expressionToFind is
not found within expressionToSearch, CHARINDEX returns 0.
CHARINDEX performs
comparisons based on the collation of the input. To perform a comparison in a
specified collation, you can use COLLATE to apply an explicit collation to the
input.
The starting position
returned is 1-based, not 0-based.
0x0000 (char(0)) is an undefined character in Windows
collations and cannot be included in CHARINDEX.
Supplementary Characters (Surrogate Pairs)
When using SC collations, both start_location and
the return value count surrogate pairs as one character, not two. For more information,
see Collation and
Unicode Support.
A.
Returning the starting position of an expression
The following example returns
the position at which the sequence of characters bicycle starts in
the DocumentSummary column of the Documenttable in the
AdventureWorks2012 database.
DECLARE @document varchar(64);
SELECT @document = 'Reflectors are vital safety' +
' components of your bicycle.';
SELECT CHARINDEX('bicycle', @document);
GO
Here is the result set.
-----------
48
B.
Searching from a specific position
The following example uses
the optional start_location parameter to start looking for vital at
the fifth character of the DocumentSummarycolumn in the AdventureWorks2012
database.
DECLARE @document varchar(64);
SELECT @document = 'Reflectors are vital safety' +
' components of your bicycle.';
SELECT CHARINDEX('vital', @document, 5);
GO
Here is the result set.
-----------
16
(1 row(s) affected)
C.
Searching for a nonexistent expression
The following example shows
the result set when expressionToFind is not found within expressionToSearch.
DECLARE @document varchar(64);
SELECT @document = 'Reflectors are vital safety' +
' components of your bicycle.';
SELECT CHARINDEX('bike', @document);
GO
Here is the result set.
-----------
0
(1 row(s) affected)
D.
Performing a case-sensitive search
The following example
performs a case-sensitive search for the string 'TEST' in 'This
is a Test'.
USE tempdb;
GO
--perform a case sensitive search
SELECT CHARINDEX ( 'TEST',
'This is a Test'
COLLATE Latin1_General_CS_AS);
Here is the result set.
-----------
0
The following example
performs a case-sensitive search for the string 'Test' in 'This
is
a Test'.
USE tempdb;
GO
SELECT CHARINDEX ( 'Test',
'This is a Test'
COLLATE Latin1_General_CS_AS);
Here is the result set.
-----------
11
E.
Performing a case-insensitive search
The following example
performs a case-insensitive search for the string 'TEST' in 'Das
ist ein Test'.
USE tempdb;
GO
SELECT CHARINDEX ( 'TEST',
'This is a Test'
COLLATE Latin1_General_CI_AS);
GO
Here is the result set.
-----------
11
PATINDEX (Transact-SQL):
Returns the starting position of the first occurrence of a
pattern in a specified expression, or zeros if the pattern is not found, on all
valid text and character data types.
Pattern
Is a character expression
that contains the sequence to be found. Wildcard characters can be used;
however, the % character must come before and follow pattern
(except when you search for first or last characters).
pattern is an expression of the
character string data type category.
pattern is limited to 8000
characters.
Expression
expression is of the character string
data type category.
bigint if expression is
of the varchar(max) or nvarchar(max)
data types; otherwise int.
If either pattern or
expression is
NULL, PATINDEX returns NULL.
PATINDEX performs comparisons
based on the collation of the input. To perform a comparison in a specified
collation, you can use COLLATE to apply an explicit collation to the input.
Supplementary Characters
(Surrogate Pairs)
When using SC collations, the
return value will count any UTF-16 surrogate pairs in the expression
parameter as a single character.
0x0000 (char(0))
is an undefined character in Windows collations and cannot be included in
PATINDEX.
A. Using a pattern with PATINDEX
The following example finds
the position at which the pattern ensure starts in a specific row of the DocumentSummary
column in the Document table in the AdventureWorks2012
database.
SELECT
PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
Here is the result set.
-----------
64
B. Using
wildcard characters with PATINDEX
The following example uses %
and _ wildcards to find the position at which the pattern 'en',
followed by any one character and 'ure' starts in the specified string (index
starts at 1):
SELECT PATINDEX('%en_ure%', 'please ensure
the door is locked');
Here is the result set.
------------
8
PATINDEX works just like LIKE,
so you can use any of the wildcards. You do not have to enclose the pattern
between percents.
PATINDEX('a%', 'abc')
returns 1 and PATINDEX('%a', 'cba') returns 3.
Unlike LIKE, PATINDEX
returns a position, similar to what CHARINDEX does.
C. Using
COLLATE with PATINDEX
The following example uses
the COLLATE function to explicitly specify the
collation of the expression that is searched.
USE tempdb;
GO
SELECT PATINDEX ( '%ein%',
'Das ist ein Test' COLLATE Latin1_General_BIN)
;
GO
D. Using a variable to specify the pattern
The following example uses a
variable to pass a value to the pattern parameter. This example uses the
AdventureWorks2012 database.
DECLARE @MyValue varchar(10) = 'safety';
SELECT PATINDEX('%' + @MyValue + '%', DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
Here is the result set.
------------
22
E. Find the position of an underscore with PATINDEX
DECLARE @a VARCHAR(10)
SET @a = '37_21'
PRINT PATINDEX('%_%', @a) -- return 1 (false)
PRINT PATINDEX('%!%', REPLACE(@a, '_', '!')) -- return 3
(correct)
OR
We can also done with brackets: '%[_]%'
No comments:
Post a Comment