trying to modify some column data in some rows

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

trying to modify some column data in some rows

Author

Topic

gln
Starting Member

2 Posts

Posted - 2005-12-31 : 12:59:15

Trying to change some of the rows in a table specifically one column.
column type is varchar
ex. of data
current desired
$345,434.0 345434.0 (stripping out of $ & commas)
435.0 leave as is
general txt leave as is

having trouble updating data in table
tried using temp table, but update command make it so it won't see table

ex. piece of code
update currency_conversion
set currency_varchar = cast (cast (currency_varchar as money)as varchar)
select * from currency_conversion
where substring (currency_varchar from 1 for 1) = '$' ;

sql 2000
trying to do from query analyzer

thanks
glnsk8ter@yahoo.com

thanks
Glenn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-31 : 14:54:10

Here is some working code you can play with. If you have any values in currency_varchar that can't convert to money, you'll get an error. Post again if that's the case.


use pubs
set nocount on
go
create table currency_conversion (currency_varchar varchar(20))
go
insert currency_conversion
select '$345,434.0' as currency_varchar union
select '435.0'
go

print 'before update'
select * from currency_conversion


update currency_conversion set
	currency_varchar = convert(varchar,convert(money,currency_varchar))
from	currency_conversion
where	left(currency_varchar,1) = '$'

print 'after update'
select * from currency_conversion

go
drop table currency_conversion

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-02 : 01:07:08

If it is for display purpose then use convert function only

Select convert(money,currency_varchar) from yourTable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources