Page 1 of 1

REPLICATE function?

Posted: Wed Oct 30, 2013 1:58 pm
by harryrundles
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 
...

Re: REPLICATE function?

Posted: Thu Oct 31, 2013 1:23 pm
by JimKnicely
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)

Re: REPLICATE function?

Posted: Thu Oct 31, 2013 1:42 pm
by JimKnicely
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