In Oracle, LPAD function left-pads a string to the specified length with the specified characters. Note that the string is truncated
if it already exceeds the specified length.
In SQL Server, you can use an expression using RIGTH, REPLICATE and LEFT functions to get the same result as Oracle.
Oracle:
-- Left-pad string with _ to 5 characters SELECT LPAD('abc', 5, '_') FROM dual; # __abc -- Left-pad string with _ to 2 characters (string will be truncated as it is 3 characters long) SELECT LPAD('abc', 2, '_') FROM dual; # ab
SQL Server:
-- Left-pad string with _ to 5 characters (if you know that the string is always shorter) SELECT RIGHT(REPLICATE('_', 5) + 'abc', 5); # __abc -- Left-pad string with _ to 2 characters (general solution if the string can be longer) SELECT RIGHT(REPLICATE('_', 2) + LEFT('abc', 2), 2); # ab
Summary information:
Oracle | SQL Server | |
Syntax | LPAD(string, length [, pad_char]) | RIGHT(REPLICATE(pad_char, length) + LEFT(string, length), length) |
Default | Pad character is a single blank | Pad character must be specified |
Last Update: Oracle 11g R2 and Microsoft SQL Server 2012
Oracle 11g R2 SQL Language Reference
Microsoft SQL Server 2012 - Books Online
SQLines offers database administration, optimization and migration services for Oracle and SQL Server databases and applications. For more information, please Contact Us.