INSTR
Updated: 30 April 2009
Use INSTR to return an integer specifying the position of the first occurrence of one string within another.
Syntax
SELECT [wctString].[wct].[INSTR] (
<@Start, int,>
,<@Text, nvarchar(max),>
,<@SearchString, nvarchar(max),>
,<@CaseSensitive, bit,>)
Arguments
@Start
sets the starting position for each search. The @Start argument can be an expression of types that are implicitly convertible to int.
@Text
the string being searched. The @Text argument can be of data types that are implicitly convertible to nvarchar or ntext.
@SearchString
the string being searched for. The @SearchString argument can be of data types that are implicitly convertible to nvarchar or ntext.
@CaseSensitive
declares the search as being either case sensitive or case insensitive, regardless of collation . The @CaseSenstive argument must be of data types that are implicitly convertible to bit.
Return Types
int
Remarks
· If @Text is zero-length, then INSTR returns zero.
· If @Text is NULL, then INSTR returns an error.
· If @SearchString is zero-length, then INSTR returns @Start.
· If @SearchString is NULL, then INSTR returns an error.
· If @SearchString is not found, then INSTR returns a zero.
· If @SearchString is found, then INSTR returns the position at which the match was found.
· If @Start > LEN(@Text), then INSTR returns 0.
· If @Start is NULL or @Start < 1, then INSTR returns an error.
Examples
select wct.INSTR(1
,'Four score and seven years ago our fathers brought forth on this continent, a new nation, conceived in Liberty, and dedicated to the proposition that all men are created equal.'
,'fathers'
,1)
This produces the following result
-----------
36
(1 row(s) affected)