T-SQL Search String From Right / Reverse Search

DECLARE @SomeText varchar(255);
DECLARE @TextToSearch varchar(10);

SET @SomeText = 'There are 3 spaces';
SET @TextToSearch = ' ';

Print CharIndex(@TextToSearch, @SomeText);
-- Following line will search @TextToSearch in @SomeText from right
Print
        CASE WHEN CharIndex(@TextToSearch, @SomeText) > 0 THEN
            (Len(@SomeText + '-') - 1) - 
                (CharIndex(Reverse(@TextToSearch), Reverse(@SomeText)) + (Len(@TextToSearch + '-')-1) - 1) + 1
        ELSE
            0
        END

-- Output:
-- -------------------------------------
-- 6
-- 12
-- -------------------------------------
-- Tips: 
--
-- Reverse() function reverses the string
--
-- Len(@SomeText + '-') - 1, returns the actual string length even if
-- @SomeText have space at the end. Note that Len() function ignores
-- the spaces at the end of string that is why we have placed a '-'
-- at the end of string then subtracted 1 from length so that Len()
-- function should return the correct length