Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

varchar to money

convert varchar(30) column to money column

there are values already in the column
0
rgb192
Asked:
rgb192
  • 2
  • 2
2 Solutions
 
cyberkiwiCommented:
-- 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
 
rgb192Author Commented:
alter table #t2 alter column vcm money

would this work for existing table


would this create error for existing values

0
 
cyberkiwiCommented:
The prior 2 update statements gets rid of all inconvertible values.
Valid values are retained.
0
 
pankajghargeCommented:
alter table#t2 alter column money
0
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now