SQL Server how to multiply dollar with decimal?-CodePudding

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?

CodePudding user response：

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