April 26, 2007 at 12:39 pm
I need to replace multiple occurances (in one table) of a certain string of text with a different string of text.
I THINK I would use SELECT REPLACE ... but I'm not sure.. can anyone help????
April 26, 2007 at 1:05 pm
Here is how you can use the REPLACE command:
UPDATE [tablename]
SET [columnname] = Replace([columnname],'existing string value','new string value')
obviously you can use the WHERE clause to limit what data is searched.
Here is some test code:
if exists (select 1
from sysobjects
where id = object_id('test_')
and type = 'U')
drop table test_
create table test_ (col1_ char(100), col2_ char(100))
insert into test_ values ('Col1 First test','Col2 just testing')
insert into test_ values ('Col1 Second test','Col2 more testing')
insert into test_ values ('Col1 Control Data','Col2 More control')
insert into test_ values ('Col1 First test','Col2 just testing')
insert into test_ values ('Col1 Second test','Col2 more testing')
insert into test_ values ('Col1 Control Data','Col2 More control')
insert into test_ values ('Col1 First test','Col2 just testing')
insert into test_ values ('Col1 Second test','Col2 more testing')
insert into test_ values ('Col1 Control Data','Col2 More control')
select * from test_
update test_ set col1_ = replace(col1_,'test','jlk')
select * from test_
update test_ set col2_ = replace(col2_,'test','jlk')
Select * from test_
April 27, 2007 at 3:16 am
Wow. Thank you so much. That helps alot!
Perhaps you can help with something a little more complicated? (I am SO hoping that I am NOT going to have to do this mannually!!!)
I have a table (about 3000 rows) where two of the columns have Domain USer information.
COL1 has DOMAIN\Username and COL2 has (or SHOULD have) <A href="mailtoOMAIN@username.com">DOMAIN@username.com
I need to look at each field in COL1 and if exists DOMAIN\username, I need to populate COL2 with <A href="mailtoOMAIN@username.com">DOMAIN@username.com
Is this possible???
April 27, 2007 at 6:36 am
--Same principal as the last example
update yourTable_
set col2_ = replace(col1_,'\','@') + '.com'
--You can get more specific
update yourTable_
set col2_ = replace(col1_,'DOMAIN\','DOMAIN@') + '.com'
--Or just those records where col1 begins with DOMAIN
--NOTE: I always recommend the use of a where clause, and you should always use begin transaction and Commit/rollback
update yourTable_
set col2_ = replace(col1_,'\','@') + '.com'
where col1_ like 'DOMAIN\%'
--And now in case you actually wanted to format and email address out of the information (username@domain.com) use the following:
update yourTable_
set col2_ = substring(col1_,charindex('\',col1_)+1,len(col1_) - charindex('\',col1_)) + '@' +
substring(col1_,1,charindex('\',col1_)-1) + '.com'
where col1_ like 'DOMAIN\%'
April 27, 2007 at 7:40 am
Thank you. With all this good info.. I'm going to try and figure it all out. I made a copy of the table and I'll use that to test..
All I really need to do is look at COL1 and is exists DOMAIN/username, copy to COL2 as username@domain.com so should I follow the above example - just without the replace??
(and YES, Col2 will need to end up with email addresses ... username@domain.com and not <A href="mailtoomain@username.com">Domain@username.com ooops)
April 27, 2007 at 12:36 pm
Thanks for everything!!! Here is the code I ended up using:
UPDATE MYTABLE
SET COL2= PARSENAME(REPLACE(COL1, '\', '.'), 1) + '@' +
PARSENAME(REPLACE(COL1, '\', '.'), 2) + '.com'
WHERE COL1 LIKE '%DOMAIN\%'
go
UPDATE MYTABLE
SET COL2 = Replace(COL2,'DOMAIN','CORRECTDOMAIN')
go
What is the best way (short of backing up the entire DB) to make a copy of this Table so that It can be easily restored if the changes need to be backed out???
April 27, 2007 at 12:59 pm
Well, I recommend a backup just before you perform the change. That is always the best policy. If you just want to keep the data around for a little while without having to perform a complete restore I have used a "backup" table for that purpose:
select *
into myBackupTable
from myCurrentTable
The above will create an EXACT duplicate of the "myCurrentTable" named "myBackupTable"
If you ever need to restore your data then use the following:
update myCurrentTable
set col1 = myBackupTable.col1,
col2 = myBackupTable.col2
from myCurrentTable join myBackupTable on (myCurrentTable.keyCol = myBackupTable.keyCol)
where ....
NOTE: The "Key" column values can not have changed otherwise the join won't work. This usually isn't a problem but if your
table uses a volatile user defined key (rather than a stable surrogate key) it could be a problem. If that is case I recommend adding an identity column to the table first.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy