How to cast an expression to a value of the given data type using the CAST() function?

CAST(exp AS type) is a MySQL built-in function that casts an expression to a value of the given data type. For example:

SET @exp = '-1.99 USD';

SELECT @exp, CAST(@exp AS SIGNED INTEGER), CAST(@exp AS UNSIGNED INTEGER);
  -- +-----------+------------------------------+--------------------------------+
  -- | @exp      | CAST(@exp AS SIGNED INTEGER) | CAST(@exp AS UNSIGNED INTEGER) |
  -- +-----------+------------------------------+--------------------------------+
  -- | -1.99 USD |                           -1 |           18446744073709551615 |
  -- +-----------+------------------------------+--------------------------------+

SELECT HEX(18446744073709551615);
  -- +---------------------------+
  -- | HEX(18446744073709551615) |
  -- +---------------------------+
  -- | FFFFFFFFFFFFFFFF          |
  -- +---------------------------+

SELECT @exp, CAST(@exp AS DECIMAL(8,4)), CAST(@exp AS FLOAT(4));
  -- +-----------+----------------------------+------------------------+
  -- | @exp      | CAST(@exp AS DECIMAL(8,4)) | CAST(@exp AS FLOAT(4)) |
  -- +-----------+----------------------------+------------------------+
  -- | -1.99 USD |                    -1.9900 |                  -1.99 |
  -- +-----------+----------------------------+------------------------+

SELECT NOW(), CAST(NOW() AS YEAR), CAST(NOW() AS TIME(6));
  -- +---------------------+---------------------+------------------------+
  -- | NOW()               | CAST(NOW() AS YEAR) | CAST(NOW() AS TIME(6)) |
  -- +---------------------+---------------------+------------------------+
  -- | 2023-12-16 00:44:49 |                2023 | 00:44:49.000000        |
  -- +---------------------+---------------------+------------------------+

Reference information of the CAST() function:

CAST(exp AS type): val
  Casts an expression to a value of the given data type. 

Arguments, return value and availability:
  exp: Required. The expression to be converted.
  type: Required. The data type to be converted to.
  val: Return value. The converted value of the given type.
  Available since MySQL 4.0.

Data types that are supported:
  Type               Return Value
  ----               -----------
  BINARY(N)          VARBINARY(N) 
  CHAR(N)            VARCHAR(N)
  DATE               DATE
  DATETIME(M)        DATETIME(M)
  DECIMAL(M,D)       DECIMAL(M,D)
  DOUBLE             DOUBLE 
  FLOAT(p)           FLOAT(p)
  JSON               JSON
  NCHAR(N)           NVARCHAR(N)
  REAL               REAL
  SIGNED INTEGER     BIGINT
  TIME(M)            TIME(M)
  UNSIGNED INTEGER   BIGINT
  YEAR               YEAR

Related MySQL functions:

• CAST() - Casting Expression to Value
• CONVERT() - Character Set Conversion

⇒ COALESCE() - Finding First Non-NULL Value

⇐ MySQL Functions for Miscellaneous Purposes

⇑ MySQL Functions for Miscellaneous Purposes

⇑⇑ MySQL Function References