Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL separate units and decimals from price

I have price column in MySQL contains this values :

price
1020.2200
202.0000
0.5000
820.6700

I want to split at the floating point.

Here is what I’m trying to get :

price p_units p_decimals
1020.2200 1020 22
202.0000 202 00
0.5058 0 50
820.6700 820 67

This is the SQL statement I’ve written so far :

SELECT 
    price, FLOOR(price) AS `p_units`, `price` MOD 1 AS `p_decimal` 
FROM 
    mytable

The problem is the results for p_decimal results is with "leading 0." and "4 decimal digits" instead of 2 :

0.2200
0.0000
0.5058
0.6700
Tags:

3

Answers


  1. 0

    Floor and a bit of processing should give the expected result.

    FIDDLE

    SELECT 
    price,
    FLOOR(price) AS p_units, 
    LPAD(FLOOR((price - FLOOR(price)) * 100), 2, '0') AS p_decimals
FROM 
    mytable;
    • The FLOOR extracts the integer part (p_units).
    • The fractional part is isolated and multiplied by 100, then padded with zeros if necessary to ensure exactly two digits for p_decimals.
    Login or Signup to reply.
  2. 0 
    SELECT
   price,  
   CAST(price AS SIGNED) AS p_units, 
   LPAD(SUBSTRING_INDEX(price, '.', -1), 2, '0') AS p_decimals 
FROM 
   YOUR_TABLE_NAME;

    • CAST(price AS SIGNED) : This converts the price value into an integer by removing the decimals.

      • CAST(1020.2200 AS SIGNED) -> 1020
      • CAST(202.0000 AS SIGNED) -> 202

    • SUBSTRING_INDEX(price, '.', -1) : Extract the part of the price after the decimal point.

      • SUBSTRING_INDEX(1020.2200, '.', -1) -> 2200
      • SUBSTRING_INDEX(202.0000, '.', -1) -> 0000

    • LPAD(..., 2, '0') : Ensure the extracted value is at least 2 characters length by padding with zeros on the left.

      • LPAD('2200', 2, 0) -> 22
      • LPAD('6', 2, 0) -> 60
      • LPAD('0000', 2, 0) -> 00
    Login or Signup to reply.
  3. 0

    Using the simple approach

    • FLOOR: Returns the largest integer value not greater than X.
    • ROUND(X,D) Rounds the argument X to D decimal places.
    select 
    price,
    floor(price) as p_units,
    floor((price-floor(price))*100) as p_decimals,
    round((price-floor(price))*100,0) as p_decimals2
from mytable

    The p_decimals2 is when you want to round the 0.5058 up to 51

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search