May 19, 2011 at 7:45 am
Hi,
I'm pretty new to SQL and this is probably quite a simple one but I would appreciate any help anyone can give me. I have a large data table on a SQL 2005 server and one of the columns is populated with different integers. I need to write a script (or whichever method is the easiest) to replace all occurrences of an integer with another integer. For example it could be every cell in that column that contains a '7' may need to be changed to '82'. I will be running the script myself so the original and new integer can be hard coded and I can change the values before running the script when I need to (it doesn't need to be anything fancy). I'm guessing I'll need to do this as a stored procedure, I have edited some stored procedures that someone else has written but they have all been returning values and not making any changes so I am uneasy with trying to work this code out myself at the moment!
Thanks in advance for any help,
Chris.
May 19, 2011 at 7:55 am
I assume the column is of varchar datatypes here.
UPDATE Tbl SET Col = REPLACE(Col, '7', '82')
May 19, 2011 at 12:16 pm
That won't really get what this guy is after. For example that would turn '17' into '182'. You probably just need to
update table set col = '82' where col = '7'
I only bring this up since the OP is clearly new and should understand the difference of these two ways of "skinning the cat". 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 19, 2011 at 2:38 pm
Or I didn't fully understand the real newbieness level here :-D. I just hope he got what he needed.
May 19, 2011 at 11:19 pm
chris.skillen (5/19/2011)
Hi,I'm pretty new to SQL and this is probably quite a simple one but I would appreciate any help anyone can give me. I have a large data table on a SQL 2005 server and one of the columns is populated with different integers. I need to write a script (or whichever method is the easiest) to replace all occurrences of an integer with another integer. For example it could be every cell in that column that contains a '7' may need to be changed to '82'. I will be running the script myself so the original and new integer can be hard coded and I can change the values before running the script when I need to (it doesn't need to be anything fancy). I'm guessing I'll need to do this as a stored procedure, I have edited some stored procedures that someone else has written but they have all been returning values and not making any changes so I am uneasy with trying to work this code out myself at the moment!
Thanks in advance for any help,
Chris.
Before you begin such updates, you need to read about BEGIN TRANSACTION. It will save your professional career.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2011 at 1:37 am
OK, thanks for all of you taking time to help. I'm going to create something to test this on before letting it loose on my main data (which I will backup anyway)! I'll report back once I've tried it. Thanks again,
Chris.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply