Saturday, March 13, 2010

Get the Last charindex of a char in a string

DECLARE @TempTable TABLE (NewPK INT identity(1,1), Customer VARCHAR(100) NOT NULL)
DECLARE @SearchChars VARCHAR(10)

SET @SearchChars = '-'

INSERT
INTO  @TempTable (Customer)
SELECT 'BabuKumarasamy-100313-0001' UNION ALL
SELECT 'BabuKumarasamy-1003313-00110' UNION ALL
SELECT 'KrishnaMoorthi-Konganapuram-0020' UNION ALL
SELECT 'Saravanan-100313-0006' UNION ALL
SELECT 'Sankar-100313-0001'

SELECT (LEN(Customer) - CHARINDEX(@SearchChars, reverse(Customer))) AS LastIndex,
       SUBSTRING (Customer, 0, (LEN(Customer) - CHARINDEX(@SearchChars, reverse(Customer))) + 1) AS StringBeforeTheIndex,
       SUBSTRING (Customer,(LEN(Customer)- CHARINDEX(@SearchChars, reverse(Customer))) + 2, LEN(Customer) - (LEN(Customer) - CHARINDEX(@SearchChars, reverse(Customer)))) AS StringAfterTheIndex
FROM   @TempTable
WHERE  Customer LIKE 'BabuKumarasamy%'

No comments: