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
.
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)