Login     Register

        Contact Us     Search

XLeratorDB/strings Online Documentation

SQL Server INSTR function


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)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service