INSTRREV

Updated 2023-11-10 19:37:18.637000

Syntax

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

Description

Use the scalar function INSTRREV to return an integer specifying the position of an occurrence of one string within another, from the end of string.

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.

Examples

select wct.INSTRREV(
'Once upon a midnight dreary, while I pondered, weak and weary, Over many a quaint and curious volume of forgotten lore, While I nodded, nearly napping, suddenly there came a tapping, As of some one gently rapping, rapping at my chamber door. Tis some visitor, I muttered, tapping at my chamber door- Only this, and nothing more.'
,'door'
,NULL
,0);

This produces the following result.

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