[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • 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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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