Replace substring specifically at the end of the character string in SQL

DECLARE @t TABLE(SomeString varchar(100))

INSERT @t VALUES ('violence begets violence'), ('the fox jumped over the fence'), ('Can I kick the can');

WITH CTE1 AS (
    SELECT
        CHARINDEX(' ', SomeString) AS LeftIndex,
        REVERSE(CHARINDEX(' ', SomeString)) AS RightIndex,
        RTRIM(SomeString) AS SomeString
    FROM
        @t
), CTE2 AS (
    SELECT
       LEFT(SomeString, LeftIndex-1) AS LeftWord,
       RIGHT(SomeString, RightIndex-1) AS RightWord,
       LeftIndex, RightIndex, SomeString
    FROM  
       CTE1
)
SELECT 
    CASE
        WHEN LeftWord <> RightWord THEN SomeString
        ELSE LEFT(SomeString, LEN(SomeString)-RightIndex)
    END
FROM
    CTE2;

The following ignores trailing spaces. If that is not a problem, please give it a try:

SELECT
  acolumn = CASE
    WHEN LEN(acolumn) < LEN(@substring)
    THEN acolumn
    ELSE LEFT(acolumn, x.cutpos)
       + REPLACE(SUBSTRING(acolumn, x.cutpos + 1, 999999), @substring, '')
  END
FROM atable
CROSS APPLY (SELECT LEN(acolumn) - LEN(@substring)) x (cutpos)
;

The query works like this:

1. If the length of the column value is less than the length of the argument substring, the entire column value is returned.

2. Otherwise, the original value is divided into two pieces: the same number of characters as in the argument is cut out from the right, the rest of the value forms the left part.

3. If the right part "contains" (but in fact is equal to) the argument, it is replaced with an empty string, otherwise it remains unchanged.

4. In either event, the result is concatenated back to the left part of the original value to form the final output value.

There's also a SQL Fiddle for you to play with.