LPAD - Left-Pad String - Oracle to SQL Server Migration

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

LPAD Conversion Overview

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

Resources

Oracle 11g R2 SQL Language Reference

Microsoft SQL Server 2012 - Books Online

SQLines Services

SQLines offers database administration, optimization and migration services for Oracle and SQL Server databases and applications. For more information, please Contact Us.

You could leave a comment if you were logged in.