Insert Spaces Into a Character String: Quick Tip

Posted November 20, 2018 by Jim Knicely, Vertica Principal Solution Architect

Rear view of two partially unrecognizable men sitting in front of a computer. One of them is pointing at a screen where are several lines of computer code.
In many SQL relational database you will have to use the RPAD function to insert spaces into a character string. That also works in Vertica. However, for a more robust solution, Vertica provides the built-in function SPACE which returns the specified number of blank spaces.

Example: dbadmin=> SELECT 'ABC' || RPAD(' ', 10, ' ') || 'DEF' "Insert 10 spaces!"; Insert 10 spaces! ------------------- ABC DEF (1 row) dbadmin=> SELECT 'ABC' || SPACE(10) || 'DEF' "Insert 10 spaces!"; Insert 10 spaces! ------------------- ABC DEF (1 row) dbadmin=> SELECT 'My girlfriend broke up with me because she said she needed ' || SPACE(10) || '!?!?' AS "?"; ? --------------------------------------------------------------------------- My girlfriend broke up with me because she said she needed !?!? (1 row) Helpful Links:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/SPACE.htm

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/RPAD.htm

Have fun!