SQL Server Forums
Profile | Register | 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
 New Topic  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

3323 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
52249 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  
 New 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.11 seconds. Powered By: Snitz Forums 2000