SQL Server Performance Forum – Threads Archive

changing the format of some data in a table

Trying to change some of the rows in a table.
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
[email protected]

---

declare @s varchar(12)
set @s=’$345,434.0′
select cast(@s as money) as ‘Numbers’
Madhivanan Failing to plan is Planning to fail

---

What do you mean by "general txt leave as is"? I don’t see any text here.
Are you trying to change the underlying data type for that column? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

I think this will do what I want, so far my little tests seem ok. update currency_conversion
set currency_varchar = case
when left (currency_varchar, 1) = ‘$’
then
cast ( cast(currency_varchar as money)as varchar)
else currency_varchar
end

---

And also make sure that it doesnt have any other alphabets [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

]]>