REPLICATE function?

Moderator: NorbertKrupa

Post Reply
harryrundles
Intermediate
Intermediate
Posts: 96
Joined: Thu Jul 19, 2012 12:33 am

REPLICATE function?

Post by harryrundles » Wed Oct 30, 2013 1:58 pm

Is there a REPLICATE function in Vertica similar to the one in MS SQL Server?

Example from my SQL Server DB:

Code: Select all

USE AdventureWorks2012;
GO
SELECT [Name]
, REPLICATE('0', 4) + [ProductLine] AS 'Line Code'
FROM [Production].[Product]
WHERE [ProductLine] = 'T'
ORDER BY [Name];
GO

Code: Select all

Name                                               Line Code
-------------------------------------------------- ---------
HL Touring Frame - Blue, 46                        0000T 
HL Touring Frame - Blue, 50                        0000T 
...
Thanks,
Harry

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: REPLICATE function?

Post by JimKnicely » Thu Oct 31, 2013 1:23 pm

You can use the LPAD function like this:

Code: Select all

dbadmin=> SELECT LPAD('T', 5, '0');
 LPAD
-------
 0000T
(1 row)
There is also an RPAD function:

Code: Select all

dbadmin=> SELECT RPAD('T', 5, '0');
 RPAD
-------
 T0000
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: REPLICATE function?

Post by JimKnicely » Thu Oct 31, 2013 1:42 pm

Actually, you might be more interested in the REPEAT function:

Code: Select all

dbadmin=> SELECT repeat('0', 4) || 'T';
 ?column?
----------
 0000T
(1 row)
Refer to: viewtopic.php?f=63&t=627
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL Functions”