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

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

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
0
serigne
Asked:
serigne
  • 2
  • 2
1 Solution
 
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
 
Gustavo Perez BuenrostroCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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