By /

How do I add a zero in front of a string in SQL? 2 Easy Ways and 1 Weird Way

There are several ways to add a zero to a string in SQL, with minor syntax differences depending on what database you’re using.

Let’s start with the easiest method, string concatenation with sample code for SQL Server, Oracle, Postgres and SQLite to add a single leading ‘0’ to an existing string ‘999’

Use the SQL + or || concatenation operator

SELECT '0' + '999' -- SQL Server, SQLite
SELECT '0' || '999' FROM DUAL -- Oracle 
SELECT '0' || '999' -- Postgres, SQLite

0999

Note: In Oracle SQL, the “FROM DUAL” statement is used to select a constant value from a dummy table called “DUAL”. Oracle SQL uses this special one-row, one-column table, which does not exist in the database, as a placeholder for performing calculations or returning a single value.

Use the CONCAT() string function

SELECT CONCAT('0','999') -- SQL Server, MySQL, Postgres
SELECT CONCAT('0','999') FROM DUAL -- Oracle

0999

SQLite doesn’t support the CONCAT function, so use the || (double pipe) concatenation operator.

Which should you use, CONCAT or + || ? Personally, I use the shorter + || operators because I find them to be easier to read, but that’s just my preference.

Obviously, you can use the same method to add a trailing zero to a string column by flipping the arguments around:

SELECT CONCAT('999', '0') -- SQL Server, MySQL, Postgres
SELECT '999' + '0' -- SQL Server, SQLite

9990

The Weird Way To Add A Leading Zero

Use the CHR() function to insert the ASCII character for zero, CHR(48).

SELECT CHR(48) || '999' FROM DUAL -- Oracle
SELECT CHR(48) || '999' -- Postgres
SELECT CHAR(48) || '999' -- SQL Server

0999

There’s only one reason I can think of where I would use this version and that’s to drive my co-workers nuts during a code review. 😈

It really isn’t intuitive what the code is doing, so stay away from this one kids.

Scroll to Top