Solved

varchar to money

Posted on 2010-11-22
5
407 Views
Last Modified: 2012-06-21
convert varchar(30) column to money column

there are values already in the column
0
Comment
Question by:rgb192
  • 2
  • 2
5 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34192453
-- setup test table with bad data, blanks, scientific value, null
create table #t2 (vcm varchar(30))
insert #t2 values ('bad')
insert #t2 values ('1e2')
insert #t2 values ('1.2')
insert #t2 values ('1.22222222')
insert #t2 values ('123')
insert #t2 values ('b123')
insert #t2 values ('b123')
insert #t2 values ('')
insert #t2 values (null)

-- your 3-statement script
update #t2 set vcm = null where ISNUMERIC(vcm)<>1
update #t2 set vcm = CONVERT(varchar(30), convert(float, vcm))
alter table #t2 alter column vcm money

-- verify results
select * from #t2
0
 

Author Comment

by:rgb192
ID: 34192644
alter table #t2 alter column vcm money

would this work for existing table


would this create error for existing values

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 125 total points
ID: 34192682
The prior 2 update statements gets rid of all inconvertible values.
Valid values are retained.
0
 
LVL 1

Assisted Solution

by:pankajgharge
pankajgharge earned 125 total points
ID: 34195489
alter table#t2 alter column money
0
 

Author Closing Comment

by:rgb192
ID: 34199517
thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question