replacing one string of text in a table with another
-
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????
-
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_
-
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="mailto
OMAIN@username.com">DOMAIN@username.comI need to look at each field in COL1 and if exists DOMAIN\username, I need to populate COL2 with <A href="mailto
OMAIN@username.com">DOMAIN@username.com Is this possible???
-
--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\%'
-
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="mailto
omain@username.com">Domain@username.com ooops) -
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???
-
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