Cart

SQL Left Pad (LPAD) Function: Emulated and Built-In

Published by Cristian Scutaru on

One single generic SQL Left Pad string function (LeftPad) maps to either a built-in LPAD function or its emulation with a SQL expression.This article walks through in detail over the generated SQL when LPAD was supported or not. Select LeftPad from our visual SQL query builder, and it will be translated into the appropriate call.

Table of Contents

SQL Left Pad Function

The visual SQL query builder of Data Xtractor or Query Xtractor has the LeftPad generic string function, which translates directly into either a LPAD call, when supported, or an emulated equivalent expression.

LeftPad fills the left part of a string with another string, repeated several times, to make a new string with the length passed as parameter. In Data Xtractor, LeftPad is a contextual function, which is applied on a previous string value, field or expression. LeftPad(length, fill) applied on a str string may translate into a LPAD(str, length, fill) specific SQL call, when LPAD is supported by the database server.

We tested all supported databases with the following designed query:

SQL Left Pad (LPad)

On each column, we provide a top string value, we transform it with a specific LeftPad call, and we show the string result. The five different test cases are as it follows:

  1. Fill-in with a single character (‘x’). This could be the most frequent use case in real life.
  2. Fill-in with a repeated string of multiple characters (‘xyz’). All relational databases with built-in LPAD support implement this behavior.
  3. No matter the fill string, the top string is truncated to a length. This is a particular case, when you must make any string fit in a limited space.
  4. Edge case with top string empty.
  5. Edge case with fill string empty.

Built-In SQL Left Pad (as LPAD Function)

SQL Left Pad (LPAD) in PostgreSQL and DB2

Both PostgreSQL and IBM DB2 provide a similar implementation and return the same result for the edge cases, as expected.

In PostgreSQL and Amazon Redshift:

SELECT LPAD('123', 8, 'x') AS "1",
   LPAD('123', 8, 'xyz') AS "2",
   LPAD('123456789012', 8, 'xyz') AS "3",
   LPAD('', 8, 'xyz') AS "4",
   LPAD('123', 8, '') AS "5"

In IBM Db2, the FROM clause is mandatory and we provide an equivalent of Oracle’s “dual” table:

SELECT LPAD('123', 8, 'x') AS "1",
   LPAD('123', 8, 'xyz') AS "2",
   LPAD('123456789012', 8, 'xyz') AS "3",
   LPAD('', 8, 'xyz') AS "4",
   LPAD('123', 8, '') AS "5"
FROM sysibm.sysdummy1

SQL Left Pad (LPAD) in Oracle

Oracle is similar to PostgreSQL and DB2, but it returns empty strings for the last two edge cases. Whenever you use LPAD with an empty string, you get an empty string as result.

SELECT LPAD('123', 8, 'x') AS "1",
   LPAD('123', 8, 'xyz') AS "2",
   LPAD('123456789012', 8, 'xyz') AS "3",
   LPAD('', 8, 'xyz') AS "4",
   LPAD('123', 8, '') AS "5"
FROM dual

SQL Left Pad (LPAD) in MySQL and MariaDB

Both MySQL and MariaDB use the same syntax for the built-in LPAD string function. The FROM clause is optional, but can be used like in Oracle.

Unlike Oracle, MySQL, Amazon Aurora and MariaDB properly return the expected result for the first edge case, but return an empty string for the second.

SELECT LPAD('123', 8, 'x') AS `1`,
   LPAD('123', 8, 'xyz') AS `2`,
   LPAD('123456789012', 8, 'xyz') AS `3`,
   LPAD('', 8, 'xyz') AS `4`,
   LPAD('123', 8, '') AS `5`
FROM dual

SQL Left Pad (LPAD) in Firebird

Firebird is like the previous databases, with a native built-in LPAD function support. It returns the expected results for the last two edge cases, just like PostgreSQL and DB2.

Like Oracle, the FROM clause is mandatory and we use rdb$database with role of “dual” in Data Xtractor applications.

SELECT LPAD('123', 8, 'x') AS "1",
   LPAD('123', 8, 'xyz') AS "2",
   LPAD('123456789012', 8, 'xyz') AS "3",
   LPAD('', 8, 'xyz') AS "4",
   LPAD('123', 8, '') AS "5"
FROM rdb$database

Emulated SQL Left Pad

The following databases do not have native support for the LPAD/RPAD SQL functions. However, they will be emulated. We’ll use only server-side expressions within the SQL query, as we don’t want stored procedures or anything else installed specifically on the database server.

SQL Left Pad Emulated in SAP/Sybase SQL Anywhere

To get the fill string duplicated several times, we can call the Replicate string function in SQL Anywhere. It works with both single characters and strings with multiple characters. The left side of the duplicated string will be cut, so we just make sure the string is long enough: we always duplicate it by length.

At the end, we join the left side of the duplicated string with the left side of the context string and that’s it. It’s interesting that Left with a negative argument in SQL Anywhere just returns an empty string – as we need – and does not fail, so we are safe. The edge cases are also properly covered.

SELECT LEFT(REPLICATE('x', 8), 8 - LEN('123'))
      + LEFT('123', 8) AS "1",
   LEFT(REPLICATE('xyz', 8), 8 - LEN('123'))
      + LEFT('123', 8) AS "2",
   LEFT(REPLICATE('xyz', 8), 8 - LEN('123456789012'))
      + LEFT('123456789012', 8) AS "3",
   LEFT(REPLICATE('xyz', 8), 8 - LEN(''))
      + LEFT('', 8) AS "4",
   LEFT(REPLICATE('', 8), 8 - LEN('123'))
      + LEFT('123', 8) AS "5"

SQL Left Pad Emulated in SAP/Sybase ASE

Sybase is more tricky and a negative value in Left is not allowed. We have to explicitly return an empty string when the top string is longer than our length argument: this has been done with a CASE expression, which complicates the expression.

SELECT CASE WHEN 8 <= LEN('123') THEN ''
      ELSE LEFT(REPLICATE('x', 8), 8 - LEN('123')) END
      + LEFT('123', 8) AS [1],
   CASE WHEN 8 <= LEN('123') THEN ''
      ELSE LEFT(REPLICATE('xyz', 8), 8 - LEN('123')) END
      + LEFT('123', 8) AS [2],
   CASE WHEN 8 <= LEN('123456789012') THEN ''
      ELSE LEFT(REPLICATE('xyz', 8), 8 - LEN('123456789012')) END
      + LEFT('123456789012', 8) AS [3],
   CASE WHEN 8 <= LEN('') THEN ''
      ELSE LEFT(REPLICATE('xyz', 8), 8 - LEN('')) END
      + LEFT('', 8) AS [4],
   CASE WHEN 8 <= LEN('123') THEN ''
      ELSE LEFT(REPLICATE('', 8), 8 - LEN('123')) END
      + LEFT('123', 8) AS [5]

Last two edge cases, with empty strings, will return unusual results. This is because Sybase ASE is the only known database that may return a one-length single-space string instead of an empty string. Len(”) is 1, not 0. And ” could be ‘ ‘, with a blank inside. As consequence, the last two emulated expressions will incorrectly concatenate our not empty string with blanks.

SQL Left Pad Emulated in Microsoft SQL Server

A StackOverflow recommended emulation for SQL Server and Azure does not cover several edge cases. We’ll have to continue with our complex expressions. Which will become even more complex here, because we cannot use the emulation provided before for Sybase ASE as it is. Like SQL Anywhere and ASE, SQL Server supports the Replicate and Left string functions. But it looks like SQL Server evaluates the first Left expression even when the branch is never actually called with a negative parameter value. The query fails with a message that we cannot use Left with such a wrong value.

The fix is to move the CASE within the Left call itself, and to explicitly return a 0 value, for an empty string. The rest remains basically the same.

SELECT LEFT(REPLICATE('x', 8),
      CASE WHEN 8 <= LEN('123') THEN 0 ELSE 8 - LEN('123') END)
      + LEFT('123', 8) AS [1],
   LEFT(REPLICATE('xyz', 8),
      CASE WHEN 8 <= LEN('123') THEN 0 ELSE 8 - LEN('123') END)
      + LEFT('123', 8) AS [2],
   LEFT(REPLICATE('xyz', 8),
      CASE WHEN 8 <= LEN('123456789012') THEN 0 ELSE 8 - LEN('123456789012') END)
      + LEFT('123456789012', 8) AS [3],
   LEFT(REPLICATE('xyz', 8),
      CASE WHEN 8 <= LEN('') THEN 0 ELSE 8 - LEN('') END)
      + LEFT('', 8) AS [4],
   LEFT(REPLICATE('', 8),
      CASE WHEN 8 <= LEN('123') THEN 0 ELSE 8 - LEN('123') END)
      + LEFT('123', 8) AS [5]

Microsoft SQL Server CE – which also supports Replicate – does not have this problem, so we can use the fix provided for Sybase ASE. But SQL CE does not support the Left functions, and we will have to use Substring instead.

SELECT CASE WHEN 8 <= LEN('123') THEN ''
      ELSE SUBSTRING(REPLICATE('x', 8), 1, 8 - LEN('123')) END
      + SUBSTRING('123', 1, 8) AS [1],
   CASE WHEN 8 <= LEN('123') THEN ''
      ELSE SUBSTRING(REPLICATE('xyz', 8), 1, 8 - LEN('123')) END
      + SUBSTRING('123', 1, 8) AS [2],
   CASE WHEN 8 <= LEN('123456789012') THEN ''
      ELSE SUBSTRING(REPLICATE('xyz', 8), 1, 8 - LEN('123456789012')) END
      + SUBSTRING('123456789012', 1, 8) AS [3],
   CASE WHEN 8 <= LEN('') THEN ''
      ELSE SUBSTRING(REPLICATE('xyz', 8), 1, 8 - LEN('')) END
      + SUBSTRING('', 1, 8) AS [4],
   CASE WHEN 8 <= LEN('123') THEN ''
      ELSE SUBSTRING(REPLICATE('', 8), 1, 8 - LEN('123')) END
      + SUBSTRING('123', 1, 8) AS [5]

SQL Left Pad Emulated in SQLite

SQLite is even more tricky, as it does not have a built-in string function to duplicate a string several times. A StackOverflow solution suggests using some complex nested calls with Replace, Quote and ZeroBlob. We still had to polish our expression, as it did not cover all edge cases. Like for SQL Server CE, we used Substr as an alternative to the unsupported Left. Remark the string concatenation operator is no longer +, but ||.

SELECT SUBSTR(REPLACE(SUBSTR(
      QUOTE(ZEROBLOB((8 + 1) / 2)), 3, 8), '0', 'x'), 1, 8 - LENGTH('123'))
      || SUBSTR('123', 1, 8) AS "1",
   SUBSTR(REPLACE(SUBSTR(
      QUOTE(ZEROBLOB((8 + 1) / 2)), 3, 8), '0', 'xyz'), 1, 8 - LENGTH('123'))
      || SUBSTR('123', 1, 8) AS "2",
   SUBSTR(REPLACE(SUBSTR(
      QUOTE(ZEROBLOB((8 + 1) / 2)), 3, 8), '0', 'xyz'), 1, 8 - LENGTH('123456789012'))
      || SUBSTR('123456789012', 1, 8) AS "3",
   SUBSTR(REPLACE(SUBSTR(
      QUOTE(ZEROBLOB((8 + 1) / 2)), 3, 8), '0', 'xyz'), 1, 8 - LENGTH(''))
      || SUBSTR('', 1, 8) AS "4",
   SUBSTR(REPLACE(SUBSTR(
      QUOTE(ZEROBLOB((8 + 1) / 2)), 3, 8), '0', ''), 1, 8 - LENGTH('123'))
      || SUBSTR('123', 1, 8) AS "5"

SQL Left Pad Emulated in Microsoft Access

Microsoft Access is another very particular case. We have a String function to replicate a character a number of times. But there is no built-in function to do so with a string of multiple characters. We could call Replace with a hack to get a similar result. But this function is not accessible over OLE DB connections, as required by our Data Xtractor applications.

So we do not support, for now, LPad and RPad string function emulations in Microsoft Access.

Categories: Query Builder

Cristian Scutaru

I designed and implemented the Data Xtractor suite, with Model Xtractor, Query Xtractor, and Visual Xtractor as separate modules. I am a software architect and developer with over 30 years professional experience. I’ve been working with relational databases for almost three decades and I was constantly unhappy with the relative limitation of those tools used to connect directly to a platform, and instantly extract and display data in flexible ways.

Related Posts

Query Builder

Window Function Framing: ROWS vs RANGE vs GROUPS

Visual description of the ROWS, RANGE and GROUPS window function clauses. Understand what frame is selected when used on a set with duplicate values. Get the difference between a current row and a current group. Think in terms of either individual rows, or groups with duplicates. Use offset rows, groups or values.

Query Builder

RANGE vs ROWS: When CURRENT ROW is not a Row

Why and when ROWS CURRENT ROW and RANGE CURRENT ROW return different results. How you can visually design this SQL query with window functions and prevent this confusion.

Query Builder

Shared Inline Aggregates with Window Functions for MySQL

Design SQL queries with window functions and reusable OVER clause declarations, regardless of whether or not your database supports the standard SQL WINDOW definition. Design visual SQL queries using MySQL window functions through Data Xtractor. Generate reusable WINDOW definitions, or simply isolate a reusable OVER clause in the visual designer.