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.