Remove certain words from a string

Question

Post reply

Remove certain words from a string

webskater

SSCarpal Tunnel

Points: 4214
More actions
March 14, 2014 at 6:34 am

#396420

CREATE TABLE #tblNoise
(
Noisy varchar(100),
)
GO
INSERT INTO #tblNoise (Noisy)
SELECT 'the' UNION ALL
SELECT 'and' UNION ALL
SELECT 'of' UNION ALL
SELECT 'or'
DECLARE @STR varchar(100) = 'the institute of comics'
I need a string that looks like 'institute comics'. I can split the string on the spaces into a table but how do I rebuild a string with the noise words missing?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ManicStar SSCoach Points: 17992 More actions · Answer 1

ManicStar

SSCoach

Points: 17992

March 14, 2014 at 6:37 am

#1697117

Replace function might do what you are wanting.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Here is one way.

select @STR = REPLACE(@str, Noisy, '')

from #tblNoise

select replace(ltrim(rtrim(@str)), ' ', ' ') as Result

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Just to keep it safer, here's a modification to Sean's option.

DECLARE @STR varchar(100) = 'the foreign institute of comics'

select @STR = REPLACE(' ' + @STR + ' ', ' ' + Noisy + ' ', ' ')

from #tblNoise

select ltrim(@str) as Result

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Luis Cazares (3/14/2014)
Just to keep it safer, here's a modification to Sean's option.
DECLARE @STR varchar(100) = 'the foreign institute of comics'
select @STR = REPLACE(' ' + @STR + ' ', ' ' + Noisy + ' ', ' ')
from #tblNoise
select ltrim(@str) as Result

What do you mean about this being safer? Not saying it is or it isn't, just not sure what you mean.

You also would probably want to add an rtrim so you don't get a bunch of spaces on the right.

We could shorten this up even more.

select @STR = replace(ltrim(rtrim(REPLACE(@str, Noisy, ''))), ' ', ' ')

from #tblNoise

select @STR

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

With out the spaces added to both sides of the words, the string 'the foreign institute of comics' would become 'feign institute comics', just to give an example.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Luis Cazares (3/14/2014)
With out the spaces added to both sides of the words, the string 'the foreign institute of comics' would become 'feign institute comics', just to give an example.

Ahh yes. Didn't think about that. I figured there was something but just wasn't seeing it. Thanks for the catch.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/