Functionality of MySQL LPAD with SqlServer

This article shows how we can achieve the functionality of MySQL LPAD with SqlServer.

As SQL Server doesn't support LPAD and RPAD.

So the following example help us to achieve this

1. MySQL LPAD() :
    Query: SELECT CONVERT(CONCAT("9",LPAD(235, 7, "0")), CHAR(20))
    O/P: '90000235'
    Description: Maximum size of string is 7 characters, as in the output only 235 (colored in red) is provided and length of '235' is 3 so it will be preceeded by 4 '0000'. And 9 will be concatenated with the whole string..

2. MS-SQL Approach :
    Now in Sql Serve there is no LPAD ()
    Query: SELECT '9' + RIGHT(REPLICATE('0',7) + CAST('235' as varchar(7)),7)
    O/P: '90000235'
     Description: Here Replicate() function is used to put '0 colored in grey' that many times if the length of the string is not 7 as we specified.

In this way we can achieve LPAD() functionality in MS-Sql Server.

Ebook Download
View all


Read by 0 people
Download Now!
View all