How Can I Preserve Leading Zeros in a MySQL Numeric Column?

Maintaining Leading Zeros in MySQL Columns

Importing CSV data often leads to the loss of leading zeros in numeric fields. This article details how to restore this formatting within MySQL.

Approach 1: Leveraging ZEROFILL

The most direct solution involves altering the column's data type to include the ZEROFILL attribute:

<code class="language-sql">ALTER TABLE table_name MODIFY COLUMN interested_column numeric(8,0) ZEROFILL;</code>

This modification ensures all values are padded with leading zeros to reach the specified length (8 digits in this example).

Approach 2: Utilizing LPAD()

If modifying the data type isn't practical, the LPAD() function provides an alternative:

<code class="language-sql">SELECT LPAD(interested_column, 8, '0');</code>

LPAD() takes three arguments: the column, the target length (8), and the padding character ('0'). The query's output will display values with leading zeros to match the specified length. This method is useful for display or temporary adjustments.

The above is the detailed content of How Can I Preserve Leading Zeros in a MySQL Numeric Column?. For more information, please follow other related articles on the PHP Chinese website!