[RESOLVED] remove characters from varchar string

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

[RESOLVED] remove characters from varchar string

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2013-07-29 : 12:46:01

I have a table with a field that stores varchar value in the following format:

Q;W;A;X;G;J

D;W

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 12:55:26

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;','');

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2013-07-29 : 13:25:14

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?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-29 : 15:13:32

Can you give this a try?

UPDATE yourTable SET
	YourColumn = STUFF(REPLACE(REPLACE(REPLACE(';'+YourColumn,';W',''),';A',''),';G',''),1,1,'')

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2013-07-30 : 14:19:35

Thank you for your help - this works as expected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources