SQL Server how to multiply dollar with decimal?

Question

Given an account table that has two columns, which are account_holder and amount.

Exaample

account_holder | amount
------------------------
James Brown    | $2302.03
Phille Jackson | $5643.55

Both columns are with the type VARCHAR(20). What I need to do is to multiply the amount with 5% to get the interest.

The expected output would be like

account_holder | interest
------------------------
James Brown    | $115.10
Phille Jackson | $282.18

What I can come up with is to cast the amount to money type. And concat a dollar sign with the number after calculation. Is there any better way to do it?

Answer 1

Best approach is to change the data type of your column. Since you can not change data type , you can try like below :

CREATE TABLE Accounts (
  account_holder  Varchar(20) NOT NULL,
  Amount Varchar(20)
);

INSERT INTO Accounts (account_holder, Amount) VALUES ('James Brown', '$2302.03');
INSERT INTO Accounts (account_holder, Amount) VALUES ('Phille Jackson', '$5643.55');

-- 1. Remove $
-- 2. Convert to decimal
-- 3. Get Interest
-- 4. Again convert to decimal with 2 places
-- 5. Concat $ 

SELECT account_holder, Amount, 
CONCAT('$', CONVERT(DECIMAL(10,2),(CONVERT(DECIMAL(10,2), RIGHT(Amount, len(AMOUNT)-1)))/20)) Interest
FROM Accounts