Course 161 - Transact-SQL Programming: SQL Server 2008/R2
Chapter 4 - Ch04_15_Strings
Purpose of the code - to return the last index of a search term in a string.
-- This is the original. It does
not work as intended. It has two flaws.
CREATE FUNCTION [dbo].[LastIndexOf](@InputString
VARCHAR(MAX), @SearchTerm CHAR)
RETURNS INT
AS
BEGIN
IF dbo.IsNullOrEmpty(@InputString) = 1 OR dbo.IsNullOrEmpty(@SearchTerm) = 1
RETURN 0
DECLARE @pos INT
SELECT @pos = LEN(@InputString) - CHARINDEX(@SearchTerm, REVERSE(@InputString)) + 1
IF @pos > LEN(@InputString)
SET @pos = 0
RETURN @pos
END
GO
DECLARE @InputString VARCHAR(MAX) = 'Scott Whigham is a Whigham
from Whigham, Georgia.'
DECLARE @SearchTerm VARCHAR(128) = 'gh'
SELECT CHARINDEX(@SearchTerm,
@InputString)
SELECT REVERSE(@InputString)
SELECT dbo.LastIndexOf(@InputString,
@SearchTerm)
GO
--This is my version
--Flaw 1 - IN THE ORIGINAL: NEED TO
CHANGE @SearchTerm to VARCHAR(Max) from a single character CHAR
--That's why the orginal code
taking 'g' not 'gh' as the searching term and the last position of g at
posision 46.
ALTER FUNCTION [dbo].[LastIndexOf](@InputString
VARCHAR(MAX), @SearchTerm VARCHAR(max))
RETURNS INT
AS
BEGIN
IF dbo.IsNullOrEmpty(@InputString) = 1 OR dbo.IsNullOrEmpty(@SearchTerm) = 1
RETURN 0
DECLARE @pos INT
--Flaw 2 - need to reverse the
@SearchTerm as well, also no '+1'
SELECT @pos = LEN(@InputString) - CHARINDEX(REVERSE(@SearchTerm), REVERSE(@InputString))
IF @pos > LEN(@InputString)
SET @pos = 0
RETURN @pos
END
GO
DECLARE @InputString VARCHAR(MAX) = 'Scott Whigham is a Whigham
from Whigham, Georgia.'
DECLARE @SearchTerm VARCHAR(128) = 'gh'
SELECT CHARINDEX(@SearchTerm,
@InputString), REVERSE(@InputString), REVERSE(@SearchTerm)
,CHARINDEX(reverse(@SearchTerm), REVERSE(@InputString))
,LEN(@InputString)
,LEN(@InputString)- CHARINDEX(reverse(@SearchTerm), REVERSE(@InputString))
,dbo.LastIndexOf(@InputString,@SearchTerm)
GO
