replacing one string of text in a table with another

Question

Post reply

replacing one string of text in a table with another

Jpotucek

SSCoach

Points: 18583
More actions
April 26, 2007 at 12:39 pm

#195427

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????

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

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_

Jpotucek SSCoach Points: 18583 More actions · Answer 2

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???

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 3

--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\%'

Jpotucek SSCoach Points: 18583 More actions · Answer 4

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)

Jpotucek SSCoach Points: 18583 More actions · Answer 5

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???

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 6

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.