Format string xxxxabc in SQL or 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('_', LEN(12abc)) + RIGHT(12abc, 3), LEN(12abc));

  # __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