asked on

SQL Server Funtion to Remove Specific Words

I have a function that removes the sizes (X-Small, Small, Medium, Large, X-Large, XX-Large, 3X-Large) from my string value. It is removing some of them but there are still that is not being removed, especially the Small and X-Small for some reason. Not sure why but I'm wondering if there is a more efficient and accurate way of removing these. Here are some of my strings, as you can see the ending is not consistent with the dashes before the sizes.

Fabric Series, Women's Short Sleeve Blue - X-Small
Fabric Series, Women's Short Sleeve Blue -Small
Fabric Series, Women's Short Sleeve Blue- Medium
Fabric Series, Women's Short Sleeve Blue-Large
Fabric Series, Women's Short Sleeve Blue-X-Small
Fabric Series, Women's Short Sleeve Blue - 3X-Large

ALTER FUNCTION [dbo].[RemoveSizes] 
(@strDescription varchar(1000))
RETURNS nvarchar(1000)
AS
BEGIN

declare @Return varchar(1000)
declare @badword varchar(1000)

set @badword = '- X-Small'
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 

set @badword = '- Small'
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

set @badword = '- Medium'
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

set @badword = '- Large'
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

set @badword = '- X-Large'
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

set @badword = '- XX-Large'
set @strDescription = CASE

Open in new window

Jim Horn

I'm really not following your code, but in general use REPLACE when you want to remove a subset of a string value.

Declare @badword CHAR(5) = 'Small'
SELECT REPLACE('abc_def_smallgoo_foo_boo_wackadoo_small_ding_a_ling', @badword, '')

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Southern_Gentleman

ASKER

Good job, Scott