Insert Spaces Into a Character String

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
edited November 20 in Vertica Tips

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/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/SPACE.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/String/RPAD.htm

Have fun!

Sign In or Register to comment.