CODEWITHSUNDEEP
sqloracle-database
jalopezp
jalopezp

Reputation: 829

LPAD a column with the MAX LENGTH of that column

I have a column of numbers of varying lengths, and I want to pad them with zeroes so that they are all as long as the largest number. Like so: 

SQL> SELECT LPAD(PROD_ID, 5, 0) AS PROD_ID  
  2  FROM PRODUCTS_TBL;  

PROD_  
----- 
11235  
00119  
00013  
00015  
00222  
00301  
00302  
00303  
00006  
00087  
00009  
00090

But I don't want to use the number 5, I want to use a function on PROD_ID, in case it changes later. I tried 

SQL> SELECT LPAD(PROD_ID, MAX(LENGTH(PROD_ID)), 0)
  2  FROM PRODUCTS_TBL;

but

SELECT LPAD(PROD_ID, MAX(LENGTH(PROD_ID)), 0)
        *
ERROR at line 1:
ORA-00937: not a single-group group function

Any suggestions?

Upvotes: 1

Views: 3349

Answers (2)

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79838

Probably best to do that in two steps. Select the maximum length first in one query, then run a second query that selects the actual data. It is possible to do it with a join, but it would probably perform poorly.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453243

I'm unable to test this myself but what if you try

SELECT LPAD(PROD_ID,
            MAX(LENGTH(PROD_ID)) OVER (),
            0)
FROM   PRODUCTS_TBL;

Upvotes: 5

Related Questions