Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Function to replace words
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 01/17/2013 :  14:12:56  Show Profile  Reply with Quote
Hi

Does anybody have function which replaces key words with blank,

i.e. have the following words in a column which I want to replace with ''

Blu R
Dvd
Blu Ray
Dvd/bl
Dvd & Blu
D/b
Dvd Box Se
Dvd & Blu Ray
D/bl
D/

So "Bad boys dvd" would become "Bad Boys"
"Batman D/B" would be come "Batman"

As I have a long list of words, it's quite messy if I use a concatenated replace statement.

Thanks

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 01/17/2013 :  17:37:54  Show Profile  Reply with Quote
Something to get you started:

Put the keywords into a table and join with that table. See the example below. If you have more than one keyword to be removed in a single sentence, the update statement would need to be run multiple times. Also, it is not quite perfect - if a keyword happens to be the starting or ending portion of a sentence, that gets replaced as well.
CREATE TABLE #keywords(word VARCHAR(32));
INSERT INTO #keywords VALUES 
('Blu R'),('Dvd'),('Blu Ray'),('Dvd/bl'),('Dvd & Blu'),('D/b'),('Dvd Box Se'),('Dvd & Blu Ray'),('D/bl'),('D/');

CREATE TABLE #test(sentence VARCHAR(255));
INSERT INTO #test VALUES ('Bad boys dvd'),('Advd'),('xyz');

UPDATE t SET 
	sentence = COALESCE(REPLACE(REPLACE(t.sentence,' '+word,''),word+' ',''),t.sentence)
FROM
	#test t
	LEFT JOIN #keywords k ON t.sentence LIKE '%'+k.word+'%'

SELECT * FROM #test;

DROP TABLE #test
DROP TABLE #keywords

Edited by - James K on 01/17/2013 17:39:18
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/17/2013 :  22:48:33  Show Profile  Reply with Quote

UPDATE t SET 
	sentence = COALESCE(REPLACE(' ' + t.sentence + ' ',' '+ word + ' ',''),t.sentence)
FROM
	#test t
	LEFT JOIN #keywords k ON ' ' + t.sentence + ' ' LIKE '% '+k.word+' %'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000