Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

varchar to money

Posted on 2010-11-22
5
Medium Priority
?
424 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 500 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 500 total points
ID: 34195489
alter table#t2 alter column money
0
 

Author Closing Comment

by:rgb192
ID: 34199517
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

879 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