CODEWITHSUNDEEP
sqlpostgresqlpostgispgadmin
AThomspon
AThomspon

Reputation: 135

How to format a numeric value in Postgresql

I have a set of code that generates a number which can be seen below

SELECT MAX(RIGHT ("node_id",3)::numeric) + 1 as newnum from sewers.structures
    WHERE(
    ST_WITHIN(
        ST_CENTROID((ST_SetSRID(structures.geom, 4326))), 
        ST_SetSRID((SELECT geom FROM sewers."Qrtr_Qrtr_Sections" WHERE "plat_page" = '510A'),4326)) ) and "node_id" != 'PRIVATE' and "node_id" !='PRIV_SAN' and "node_id" !='PRIV_STORM'

When I run this it generates a number based on the previously placed values. The out put will be a number that can be up to 3 digits. I want to take an output of less than three digits, and force it into a 3 digit format.

For example, if I generate the number 93 I would like to format it as 093. Same for single digit numbers like 2, I want it to be formated at 002 and so on. However, if it generates a 3 digit number, I want it to keep the same format, so 121 stays as 121.

Upvotes: 2

Views: 5331

Answers (2)

JGH
JGH

Reputation: 17906

Since the output will be a string, you can use to_char with a format of three 0

select to_char(1,'FM000');
 to_char
---------
 001
(1 row)

FM prefix will avoid padding a space to the left with unsigned inputs (See this and this).

Upvotes: 3

Jim Jones
Jim Jones

Reputation: 19653

If I got your question right, you're looking for lpad():

WITH j (x) AS (
  VALUES (2),(121),(93)
)
SELECT lpad(x::text,3,'0') FROM j;

 lpad 
------
 002
 121
 093
(3 rows)

Upvotes: 3

Related Questions