CODEWITHSUNDEEP
sqlpostgresql
TRKirua
TRKirua

Reputation: 11

Padding inside of a string in SQL

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 !

Upvotes: 1

Views: 67

Answers (3)

TRKirua
TRKirua

Reputation: 11

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 !

Upvotes: 0

Zegarek
Zegarek

Reputation: 26018

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.

Upvotes: 0

Isolated
Isolated

Reputation: 6454

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

Upvotes: 3

Related Questions