Using T-SQL remove specified words from a string
33,041
Why don't you just use REPLACE?
UPDATE tableName
SET columnName = REPLACE(columnName,'specific word','');
Author by
user1075081
Updated on July 05, 2022Comments
-
user1075081 about 2 years
I am trying to eliminate certain strings from a Description field in a table. To this end I have made this function
CREATE FUNCTION fnDescriptionClean (@strDescription varchar(50)) RETURNS varchar(50) AS BEGIN declare @Return varchar(50) declare @badword varchar(50) set @badword = 'Front' set @strDescription = CASE --Remove from mid string WHEN @strDescription LIKE '% ' + @Badword +' %' THEN REPLACE(@strDescription,' ' + @Badword + ' ',' ') --Remove from start of string WHEN @strDescription LIKE @Badword +' %' THEN RIGHT(@strDescription, (len(@strDescription)-(len(@Badword)+1))) --Remove from end of string WHEN @strDescription LIKE '% ' + @Badword THEN LEFT(@strDescription, (len(@strDescription)-(len(@Badword)+1))) ELSE @strDescription END set @badword = 'Right' set @strDescription = CASE WHEN @strDescription LIKE '% ' + @Badword +' %' THEN REPLACE(@strDescription,' ' + @Badword + ' ',' ') WHEN @strDescription LIKE @Badword +' %' THEN RIGHT(@strDescription, (len(@strDescription)-(len(@Badword)+1))) WHEN @strDescription LIKE '% ' + @Badword THEN LEFT(@strDescription, (len(@strDescription)-(len(@Badword)+1))) ELSE @strDescription END RETURN @strDescription end
I am new to SQL programming and would like to improve on this. Supposing I wanted to have a table which contained a list of 'bad words' that I wanted to be removed from a string and loop through it when cleaning the description.
I should point out that this process needs to be as efficient as possible as I am dealing with 15 millions records.