How to remove leading zeros in PostgreSQL?

by raven_corwin , in category: PHP Databases , 8 months ago

How to remove leading zeros in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by jasen_gottlieb , 3 months ago

@raven_corwin 

To remove leading zeros in a string column in PostgreSQL, you can use the replace function as follows:

1
SELECT replace(column_name, '^0+', '') FROM table_name;


This will remove any leading zeros from the column_name column in the table_name table.


For example, if you have a table customers with a column customer_id containing values such as 001, 002, and 003, you can remove the leading zeros as follows:

1
SELECT replace(customer_id, '^0+', '') FROM customers;


This will return the values 1, 2, and 3 for the customer_id column.


Note that the replace function will only remove leading zeros from the string. If you have zeros within the string that you want to remove, you will need to use a different approach.