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.
SELECT [wctString].[wct].[INSTRREV] (
the string being searched. The @Text argument can be of data types that are implicitly convertible to nvarchar or ntext.
the string being searched for. The @SearchString argument can be of data types that are implicitly convertible to nvarchar or ntext.
sets the starting position for each search. The @Start argument can be an expression of types that are implicitly convertible to int.
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.
· 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.
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.'
This produces the following result
(1 row(s) affected)