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.
Author |
Topic |
Steve2106
Posting Yak Master
183 Posts |
Posted - 2013-08-16 : 18:01:47
|
Hi There,I need to change the last 3 characters of a string in a column of every record in my table.Can someone tell me how I can do that.Thanks for your help.Best Regards,Steve |
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-08-16 : 18:15:01
|
SELECT STUFF(column_name, LEN(column_name) - 2, 3, <new_chars_to_change_to>)The new chars can be any number of bytes; it doesn't have to be 3. |
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2013-08-17 : 16:48:35
|
Hi Scott,Thanks for the reply.That does what I need but it just gives me a list.How do I actually overwright whats in the table with the list produced by your code.Thanks again.Best Regards,Steve |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-08-17 : 17:25:06
|
UPDATE ?UPDATE dbo.Table1SET Col1 = SUBSTRING(Col1, 1, LEN(Col1) - 3) + 'new content';Beware that LEN does not care about trailing spaces. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-18 : 04:28:28
|
[code]UPDATE dbo.Table1SET Col1 = REPLACE(Col,RIGHT(Col,3),'NewContent')[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sivadss2007
Starting Member
18 Posts |
Posted - 2013-08-28 : 07:39:23
|
UPDATE Tableset column_name = replace(column_name right(column_name,3),'new value')P.Siva |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2013-08-28 : 11:27:36
|
quote: Originally posted by visakh16
UPDATE dbo.Table1SET Col1 = REPLACE(Col,RIGHT(Col,3),'NewContent')
This method is VERY DANGEROUS as it will replace any other occurrence of that pattern with the 'NewContent'. As an example:DECLARE @t table (col varchar(50));INSERT @t values('010101010101010');INSERT @t values('100100101010100');INSERT @t values('abcdefabcefgabc');SELECT * FROM @t;/*col010101010101010100100101010100abcdefabcefgabc*/UPDATE @tSET Col = REPLACE(Col,RIGHT(Col,3),'NewContent');SELECT * FROM @t;/*colNewContent1NewContent1NewContent1NewContentNewContentNewContent101010NewContentNewContentdefNewContentefgNewContent--*/ The same goes for the solution provided by sivadss2007 (which is essentially the same).Even if you were relatively confident your strings were unique, I would still follow either Scott or SwePeso's methods as it is far more exact and reliable. |
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2013-08-28 : 11:36:38
|
Hi There,Thanks for the update.I'll be careful with this.Best Regards,Steve. |
|
|
|
|
|
|
|