Padding and Trimming in SQL

Padding

 

Padding data or in other words adding a specific set of characters to the left of the string or right of the string can be done using inbuilt T-SQL and PL/SQL functions.

 

Padding can be done in SQL Server using RIGHT and LEFT functions where as in Oracle it is done using LPAD and RPAD functions, padding is pretty straight forward in oracle but in SQL Server Right Function is used to pad specific set of characters to the left of the string and Left Function is used to pad specific set of characters to the Right of the string

 

Oracle

 

LPAD Examples:

            Select LPAD('ab',6,'0') from dual                  -- will return ‘0000ab’

            Select LPAD('ab',6) from dual                        -- will return ‘    ab’

            Select LPAD('ab',6,'012345') from dual         -- will return ‘0123ab’

 

Note: replace 'ab' with the column to which you would like to pad data

 

RPAD Examples:

            Select RPAD('ab',6,'0') from dual                   -- will return ‘ab0000’

            Select RPAD('ab',6) from dual                        -- will return ‘ab    ’

            Select RPAD('ab',6,'012345') from dual         -- will return ‘ab0123’

 

Note: replace 'ab' with the column to which you would like to pad data

 

SQL Server

 

RIGHT Examples:

            select right('000000'+'ab',6)                          -- will return ‘0000ab’

select right(ab',6)                                           -- will return ‘ab’ and it will not pad space in front

            select right('      '+'ab',6)                                -- will return ‘    ab’

            select right('012345'+'ab',6)                          -- will return ‘2345ab’

 

Note: Look at the last select statement unlike LPAD it returns ‘2345ab’ so please be careful of it, the required result can be achieved as shown below

            select left('012345',6-len('ab'))+'ab'

 

If you thought it was insane to write 0’s or Spaces as many times as you need and looking for an alternative you can use replicate function

            select right(replicate('0',6)+'ab',6)    -- will return ‘0000ab’ as you can see we are replicating ‘0’ 6 times

 

LEFT Examples:

            select left('ab'+'000000',6)                             -- will return ‘ab0000’

select left(ab',6)                                              -- will return ‘ab’ and it will not pad space in front

            select left('ab'+'      ',6)                                   -- will return ‘ab    ’

            select left('ab'+'012345',6)                             -- will return ‘ab0123’

 

Again instead of write 0’s or Spaces as many times as you need, you can use replicate function

            select right('ab'+ replicate('0',6),6)   -- will return ‘ab0000’ as you can see we are replicating ‘0’ 6 times

 

 

Trim Data:

 

Trimming data or in other words removing extra space characters to the left of the string or right of the string can also be done using inbuilt T-SQL and PL/SQL functions

 

Oracle has a left trim (ltrim), right trim (rtrim) and trim on both sides (trim), SQL Server just has left trim (ltrim) and right trim (rtrim)

 

Oracle

 

select trim('    ab    ') from dual                     --will return “ab”

select ltrim('    ab    ') from dual                    --will return “ab    ”

select rtrim('    ab    ') from dual                    --will return “    ab”

 

SQL Server

 

select ltrim('    ab    ')                                     --will return “ab    ”

select rtrim('    ab    ')                                     --will return “    ab”

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.