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.
A 30+ year database veteran. I enjoy sharing my stories and knowledge with others.