how to convert varchar data type rows into money in sql server 2008 R2?

Question

Hi ,

i got one requirement like - i need to convert var char data type column rows into money data type or int data.

when i used cast or convert function that time i am getting the below error

"Conversion failed when converting the varchar value '0.5' to data type int ."

Ex:

CREATE TABLE [dbo].[test](
[id] [int] NULL,
[amount] [varchar](50) NULL
) ON [PRIMARY]

insert test values(1,'0.5'),(2,0.4)

Expected output is:

id    amount

1       50.00

2       40.00

thanks in advance

---

franklinsentil

Answer 1

select *,[amount]*100.0 from [test]

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

Hi  franklinsentil,

CREATE TABLE [dbo].[test](
[id] [int] NULL,
[amount] [varchar](50) NULL
) ON [PRIMARY] 
insert test values(1,0.5),(2,0.4)
--if you try below query ,you will get error because you can convert varchar to int,only if your varchar column has only numbers(no alphabets/symbols)
SELECT CONVERT(INT, amount) [amount],* FROM test
--if you need conversion to int or money data type,try like below
SELECT CONVERT(INT, REPLACE(amount,'.','')) [amount],*  FROM test
SELECT CONVERT(Money, amount) [amount],*  FROM test
--for achieving expected result format,
SELECT CONVERT(numeric(10,2), amount)*100 [amount],*  FROM test
--or simply try like URI DIMAT suggestion

---

Thanks & Regards, sathya