varchar to money

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

there are values already in the column
Question by:rgb192
  • 2
  • 2
LVL 58

Expert Comment

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

Author Comment

ID: 34192644
alter table #t2 alter column vcm money

would this work for existing table

would this create error for existing values

LVL 58

Accepted Solution

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

Assisted Solution

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

Author Closing Comment

ID: 34199517

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
if and else in stored procedure 19 49
SQL Select * from 6 41
MS SQL page split per second is high 19 94
Passing value to a stored procedure 8 99
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question ( here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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