Data Conversion

I have a table having data stored in this format (varchar)
2000
699
1
00
3500
40000

I would like to convert it in this format (currency)
20.00
6.99
1.00
0.00
35.00
400.00
LVL 1
serigneAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gustavo Perez BuenrostroConnect With a Mentor Commented:
serigne,
Check query below and let me know if it works as you need, note you should be careful not to exceed column's string length.

PD: A column with data type varchar(5) was assumed.


update YourTable
   set YourColumn
      =case when isnumeric(YourColumn)=1 and datalength(YourColumn)<5
            then
            substring(convert(varchar,convert(decimal(15,2),YourColumn)/100,2)
                     ,1
                     ,charindex('.',convert(varchar,convert(decimal(15,2),YourColumn)/100,2))+2
                        )
            else YourColumn
       end
  from YourTable
0
 
chigrikCommented:
Try this:

USE pubs
GO

SET NOCOUNT ON
GO

if object_id('dbo.tbConverted') is not null drop table tbConverted
GO

CREATE TABLE tbConverted (
        ID int identity primary key,
        fldMoney varchar (20)
)
GO

insert into tbConverted VALUES ('2000')
insert into tbConverted VALUES ('699')
insert into tbConverted VALUES ('1')
insert into tbConverted VALUES ('00')
insert into tbConverted VALUES ('3500')
insert into tbConverted VALUES ('40000')
GO

select * from tbConverted
GO

ALTER TABLE tbConverted ADD fldMoney_new numeric(15,2)
GO

update tbConverted set fldMoney_new = convert(numeric(15,2), fldMoney) / 100
GO

ALTER TABLE tbConverted DROP COLUMN fldMoney
GO

sp_rename 'tbConverted.fldMoney_new', 'fldMoney'
GO

select * from tbConverted
GO

This is the results set:
"
ID          fldMoney            
----------- --------------------
1           2000
2           699
3           1
4           00
5           3500
6           40000

Caution: Changing any part of an object name could break scripts and stored procedures.
The column was renamed to 'fldMoney'.
ID          fldMoney          
----------- -----------------
1           20.00
2           6.99
3           .01
4           .00
5           35.00
6           400.00
"
0
 
Gustavo Perez BuenrostroCommented:
serigne,
You can do what you want using this query:

update YourTable
   set YourColumn
      =case when isnumeric(YourColumn)=1
            then
            substring(convert(varchar,convert(decimal(15,2),YourColumn)/100,2)
                     ,1
                     ,charindex('.',convert(varchar,convert(decimal(15,2),YourColumn)/100,2))+2
                        )
            else YourColumn
       end
  from YourTable
0
 
serigneAuthor Commented:
gpbuenrostro

When I  run your update query I get this error"
Server: Msg 8152, Level 16, State 9, Line 2

String or binary data would be truncated.
The statement has been terminated
0
 
serigneAuthor Commented:
gpbuenrostro

When I  run your update query I get this error"
Server: Msg 8152, Level 16, State 9, Line 2

String or binary data would be truncated.
The statement has been terminated
0
All Courses

From novice to tech pro — start learning today.