sql server trim word from last

SQL server trim word from last

Suppose we have a string: I am a good boy.John is bad boy

Requirement: To remove boy from last.

DECLARE @replcae_str VARCHAR(max)

DECLARE @str VARCHAR(max)

SELECT @replcae_str = 'boy'

SELECT @str = 'I am a good boy. John is bad boy'

SELECT

 SUBSTRING

  (

  @str,

  1,

  CASE CHARINDEX(REVERSE(@replcae_str),REVERSE(@str))

  when 0 then 0   

  else LEN(@str) - ( CHARINDEX(REVERSE(@replcae_str), REVERSE(@str)) + (LEN(@replcae_str) - 1) ) + 1

  END - 1

  )