MS SQL Server PADDING Function

Sometimes you may need to pad character in left or in right in MS SQL Server. There is no direct function in MS SQL Server to serve this purpose. However we can do this using two built-in functions. One is REPLICATE and another is CONCAT.

I’ll show you here two examples. One is for left padding and another is for right padding.

Left padding example:

SELECT 
    CONCAT(REPLICATE('0',20-LEN(customer_id)),customer_id) customer_id,
    customer_name
FROM 
    [DataAnalytics].[dbo].[customer_details];

Right padding example:

SELECT 
    CONCAT(customer_id,REPLICATE('0',20-LEN(customer_id))) customer_id,
    customer_name
FROM 
    [DataAnalytics].[dbo].[customer_details];

I have an another post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server.

In this tutorial, I have shown you how to make left padding and right padding using CONCAT & REPLICATE function in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like the facebook page http://www.facebook.com/freetechtrainer and stay connected.

Add a Comment