Padding zeros to the left in postgreSQL

by

You can use the rpad and lpad functions to pad numbers to the right or to the left, respectively. Note that this does not work directly on numbers, so you’ll have to use ::char or ::text to cast them:

SELECT RPAD(numcol::text, 3, '0'), -- Zero-pads to the right up to the length of 3
       LPAD(numcol::text, 3, '0')  -- Zero-pads to the left up to the length of 3
FROM   my_table

More Related Contents:

  1. PostgreSQL Crosstab Query
  2. Insert text with single quotes in PostgreSQL
  3. PostgreSQL unnest() with element number
  4. Dynamic alternative to pivot with CASE and GROUP BY
  5. Create unique constraint with null columns
  6. Optimise PostgreSQL for fast testing
  7. Fast way to discover the row count of a table in PostgreSQL
  8. Best way to check for “empty or null value”
  9. How to avoid multiple function evals with the (func()).* syntax in a query?
  10. Time zone storage in data type “timestamp with time zone”
  11. Serial numbers per group of rows for compound key
  12. Postgres analogue to CROSS APPLY in SQL Server
  13. Get the default values of table columns in Postgres?
  14. Division of integers returns 0
  15. PostgreSQL: how to convert from Unix epoch to date?
  16. Postgres not using index when index scan is much better option
  17. SQL update fields of one table from fields of another one
  18. How to store array or multiple values in one column
  19. convert Postgres geometry format to WKT
  20. Which datatype should be used for currency?
  21. How to return two columns with function
  22. COPY with dynamic file name
  23. Count cumulative total in Postgresql
  24. Prepared Statement on Postgresql in Rails
  25. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  26. PostgreSQL 9.3: Dynamic pivot table
  27. How to detect query which holds the lock in Postgres?
  28. PostgreSQL how to create a copy of a database or schema?
  29. PostgreSQL: fill NULL values in timeserie query with previous value
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment