CODEWITHSUNDEEP
mysql
Sergei Ledvanov
Sergei Ledvanov

Reputation: 2251

MySQL cast to DECIMAL with variable precision

I have a MySQL function where I have a cast to DECIMAL like this

DECLARE len INT;
SET len = 10;
CAST((COUNT(*) * 1.5) AS DECIMAL(len))

However when I run it I get error 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'len)

What I want is to use a variable len in AS DECIMAL() expression.

Upvotes: 3

Views: 732

Answers (2)

Alain
Alain

Reputation: 37004

Getting the desired number of decimals can be done through string manipulation.

With _value being the number, and _decimals the number of desired decimals:

DECLARE _result TEXT;
DECLARE _left TEXT;
DECLARE _right TEXT;

-- Ensures that the number have a decimal part
SET _value = CAST(ROUND(_value, _decimals) AS DECIMAL(65, 30));

-- Take all non-decimals
SET _left = SUBSTRING_INDEX(_value, ".", 1);

-- Take decimals, limited to _decimals characters
SET _right = RPAD(SUBSTRING_INDEX(_value, ".", -1), _decimals, "0");

-- Concatenate the result
IF CHAR_LENGTH(_right) THEN
    SET _result = CONCAT(_left, ".", _right);
ELSE
    SET _result = _left;
END IF;

Upvotes: 0

Shadow
Shadow

Reputation: 34305

MySQL does not allow variables to be used to define data, so you cannot use a variable to indicate the precision or scale of a decimal data type.

All you can do is to create the appropriate sql command by string concatenation and issue it using a prepared statement.

The only catch with prepared statements is that they are not executed in the context of your function. So, if you plan to use the results later in your function, then you may encounter some issues.

You could use the maximum allowed precision value in your function instead of trying to dynamically set it in the code, or format the output on the application side to the correct number of digits.

Upvotes: 3

Related Questions