SQL separate units and decimals from price

Question

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

Answer 1

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