[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

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

0
Southern_Gentleman
Asked:
Southern_Gentleman
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
 
Scott PletcherSenior DBACommented:
For compatability, you probably want to leave it a scalar function for now.

For efficiency, use a single RETURN SELECT with multiple embedded REPLACEs:

CREATE FUNCTION [dbo].[RemoveSizes]
(@strDescription varchar(1000))
RETURNS nvarchar(1000)
AS
BEGIN
RETURN (
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@strDescription,
        '- X-Small', ''),
        '- Small', ''),
        '- Medium', ''),
        '- Large', ''),
        '- X-Large', ''),
        '- XX-Large', ''),
        '- 3X-Large', '')
)
END --FUNCTION
0
 
Southern_GentlemanAuthor Commented:
Good job, Scott
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now