October 20, 2011

SQL Server: LPAD and RPAD functions equivalent




Oracle has two formatting functions LPAD and RPAD which formats the number by appending leading and trailing zeros respectively. SQL Server does not have direct equivalent functions.

However you can simulate these functions using other string functions available in SQL Server. Let us take a practical example. Suppose you want to export data to fixed format file and the requirement is that the number should be 10 digits long, in such a way that if the total number of digits is less than 10, the remaining digits should be filled with zeroes.

Consider the following example

lpad-rpad-sqlserver
declare @num int
set @num=872382
select
right(replicate('0',10)+cast(@num as varchar(15)),10) aS lpad_number,
left(cast(@num as varchar(15))+replicate('0',10),10) as rpad_number


The above code shows numbers in two formats. The first is left padded and second is right padded. The replicate function is used to replicate 0 for 10 times and the actual number is converted to string and then appended with zeroes.

The right function picks the last 10 digits from the result, so that it has 4 leading zeroes, as the original number shown in the code above has only 6 digits. We then reverse the same by converting a number to varchar and appending leading zeroes at the end. We then use the LEFT function which picks the number where last 4 digits are zeroes.

OUTPUT

lpad-rpad-sqlserver-demo


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

3 Responses to "SQL Server: LPAD and RPAD functions equivalent"
  1. Instigante said...
    September 27, 2012 at 1:43 PM
    This comment has been removed by the author.
  2. Thiago Anitelle said...
    September 27, 2012 at 1:49 PM

    I think Oracle programming is much more practical.

    Take a look:

    SQL Server
    right(replicate('0',5)+cast(rtrim(ltrim(@variable)) as varchar(5)),5)

    Oracle
    LPAD(TRIM(@variable),5,0)

    Anyway, thanks for the article!

    - Anitelle

  3. Thiago Anitelle said...
    September 27, 2012 at 1:49 PM
    This comment has been removed by the author.
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions