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 !
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 |
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
\D at the start of the string ^\d at the end ${0,4}() with a backreference \1 and \2.repeat() up to the total length of 4.It's good to consider additional test cases.
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 !
