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 !

CodePudding user response：

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;

CodePudding user response：

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.