question

aRookieBIdev avatar image
aRookieBIdev asked

Padding in sql

What is the equivalent for the RPAD function in SQL? Example RPAD(LTRIM(RTIM(Column1)) , 10 , '')
sql-server-2005t-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
There is not direct equivalent. You'll need to use a combination of REPLICATE or SPACE and LEFT or SUBSTRING LEFT((LTRIM(RTRIM(Column1)) + REPLICATE(' ', 10)), 10) LEFT((LTRIM(RTRIM(Column1)) + SPACE(10)), 10) A quick shortcut if you want to right pad with spaces is to convert to CHAR(10). Since CHAR strings are always right padded with enough spaces to reach their designated size, you won't have to do the pad yourself CONVERT(CHAR(10), LTRIM(RTRIM(Column1)))
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You could use the REPLICATE function i T-SQL. Let's assume we have a variable @num with a numeric value and we want to right pad it with initial zeros. REPLICATE('0',10 - LEN(cast(@num as varchar(10)))) + CAST(@num as varchar(10)) This assumes @num is less then 10 digits long.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.