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 2008 Forums
 Transact-SQL (2008)
 [RESOLVED] remove characters from varchar string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 07/29/2013 :  12:46:01  Show Profile  Reply with Quote
I have a table with a field that stores varchar value in the following format:
Q;W;A;X;G;J

or
D;W

or
W;F;S

There are a total of 12 different letters that are used separated by semi-colon and the combination can be in any sequence anywhere between 1 letter and 12 letters

I need to update these values and remove the letters W, A and G from the string no matter where in the string they appear.

Any help would be welcome. Thanks

Edited by - KlausEngel on 07/30/2013 14:20:20

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 07/29/2013 :  12:55:26  Show Profile  Reply with Quote
Use one of the two below depending on whether you want to remove the extra semi-colons also or not:
UPDATE yourTable SET
	YourColumn = REPLACE(REPLACE(REPLACE(YourColumn,'W',''),'A',''),'G','');
	

UPDATE yourTable SET
	YourColumn = REPLACE(REPLACE(REPLACE(YourColumn+';','W;',''),'A;',''),'G;','');
Go to Top of Page

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 07/29/2013 :  13:25:14  Show Profile  Reply with Quote
Thank you - I do need to remove the semi-colon as well so your second query is helpful. However, when on e of the letters is in the last position the semi-colon does not get removed. How would I need to alter the query to accomodate for this situation?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 07/29/2013 :  15:13:32  Show Profile  Reply with Quote
Can you give this a try?
UPDATE yourTable SET
	YourColumn = STUFF(REPLACE(REPLACE(REPLACE(';'+YourColumn,';W',''),';A',''),';G',''),1,1,'')
Go to Top of Page

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 07/30/2013 :  14:19:35  Show Profile  Reply with Quote
Thank you for your help - this works as expected.
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.05 seconds. Powered By: Snitz Forums 2000