Question 90723 in PostgreSQL Question posted in
The official documentation can be found here.

Padding inside of a string in SQL – Postgresql

I just started learning SQL and there is my problem.
I have a column that contains acronyms like "GP2", "MU1", "FR10", …. and I want to add ‘0’s to the acronyms that don’t have enough characters.

For example I want acronyms like "FR10", "GP48",… to stay like this but acronyms like "MU3" must be converted into "MU03" to be as the same size as the others.

I already heard about LPAD and RPAD but it just add the wanted character at the left or the right.

Thanks !

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you all for your response. I think i did something similar as Isolated. Here is what I've done ("acronym" is the name of the column and "destination" is the name of the table) :

    SELECT CONCAT(LEFT(acronym, 2), LPAD(RIGHT(acronym, LENGTH(acronym) - 2), 2, '0')) AS acronym
FROM destination
ORDER BY acronym;

    Thanks !


  2. 0

    Is the minimum length 3 as in your examples and the padded value should always be in the 3rd position? If so, use a case expression and concat such as this:

    with my_data as (
  select 'GP2' as col1 union all
  select 'MU1' union all
  select 'FR10'
  )
select col1, 
 case
  when length(col1) = 3 then concat(left(col1, 2), '0', right(col1, 1))
  else col1
 end padded_col1
from my_data;
    col1 padded_col1
    GP2 GP02
    MU1 MU01
    FR10 FR10
    Login or Signup to reply.
  3. 0

    A regexp_replace():

    with tests(example) as (values
('ab02'),('ab1'),('A'),('1'),('A1'),('123'),('ABC'),('abc0'),('a123'),('abcd0123'),('1a'),('a1a'),('1a1') )
select example,
       regexp_replace(
           example,
           '^(D{0,4})(d{0,4})$',
           '1' || repeat('0',4-length(example)) || '2' )
from tests;

 example  | regexp_replace
----------+----------------
 ab02     | ab02
 ab1      | ab01
 A        | A000
 1        | 0001
 A1       | A001
 123      | 0123
 ABC      | ABC0
 abc0     | abc0
 a123     | a123
 abcd0123 | abcd0123 --caught, repeat('0',-4) is same as repeat('0',0), so nothing
 1a       | 1a   --doesn't start with non-digits 
 a1a      | a1a  --doesn't end with digits
 1a1      | 1a1  --doesn't start with non-digits
    1. catches non-digits with a D at the start of the string ^
    2. catches digits with a d at the end $
    3. specifies that it’s looking for 0 to 4 occurences of each {0,4}
    4. referencing each hit enclosed in consecutive parentheses () with a backreference 1 and 2.
    5. filling the space between them with a repeat() up to the total length of 4.

    It’s good to consider additional test cases.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search