INSTR

Updated 2023-06-15 21:41:09.903000

Syntax

SELECT [westclintech].[wct].[INSTR] (
   <@Start, int,>
 ,<@Text, nvarchar(max),>
 ,<@SearchString, nvarchar(max),>
 ,<@CaseSensitive, bit,>)

Description

Use the scalar function INSTR to return an integer specifying the position of the first occurrence of one string within another.

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.

@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.

@SearchString

the string being searched for. The @SearchString argument can be of data types that are implicitly convertible to nvarchar or ntext.

Return Type

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.

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"36"}]}