INSTRREV
Updated: 30 April 2009
Use INSTRREV to return an integer specifying the position of an occurrence of one string within another, from the end of string.
Syntax
SELECT [wctString].[wct].[INSTRREV] (
<@Text, nvarchar(max),>
,<@SearchString, nvarchar(max),>
,<@Start, int,>
,<@CaseSensitive, bit,>)
Arguments
@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.
@Start
sets the starting position for each search. The @Start argument can be an expression of types that are implicitly convertible to int.
@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.
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
-----------
310
(1 row(s) affected)