updating - find and replace - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating - find and replace

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-05-30 : 10:00:00

Basically I want to do a find and replace.
All the rows where column1 starts with F:\ I want to replace with M:
I tried this without luck

update table
set left(column1,1)='M'
where
left(column1,1)='F'

thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-30 : 10:02:38

[code]Update Table
Set column = Replace(column, 'F:\', 'M:\')[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-05-30 : 10:16:26

Thanks really appreciate your swift response.
What exactly does the replace function do?
I ran a query looking for all records that started with 'F:\' and got 60,000.
When I did the replace query it updated 85,000 rows

very puzzling???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 10:20:55

this will only replace records that start with 'F:\'


Update Table
Set column = Replace(column, 'F:\', 'M:\')
where column like 'F:\%'

replace() will replace all occurance of the F:\ in the colun with M:\. Meaning if you have a string like 'CF:\' it will be replace to 'CM:\'

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-05-30 : 10:24:20

Oh yes of course, thanks for that

Kristen
Test

22859 Posts

Posted - 2007-05-30 : 13:42:32

Update MyTable
Set MyColumn = 'M' + SUBSTRING(MyColumn, 2, DATALENGTH(MyColumn))
where MyColumn like 'F:\%'

has more localised replace

(Slightly loose syntax on the DATALENGTH() function!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources