asked on

PL/SQL equivalent of T-SQL REPLICATE statement

I have the following code in a T-SQL script and it works well to put asterisks in to hide the code number.

SUBSTRING(columnA, 1, 1) + REPLICATE('*', Length(columnA) - 5) + SUBSTRING(columnA, Length(columnA) - 3, 4)

I have to update the DTS package that pulls the values from Oracle and I want to transform the code number during the select.

I have the script changed to:

SUBSTR(columnA, 1, 1) || '********' || SUBSTR(columnA, Length(columnA) - 3, 4)

This works BUT instead of hardcoding the '*****' I'd rather just put in a replication of asterisks for the LENGTH - 5 of the columnA.

How can I do that in the DTS package or in Oracle PL/SQL that's going through an ODBC connection? I DON'T have the capability of creating a package to write a DUPLICATE function.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

safair

ASKER

A friend suggested RPAD, which was a similar solution. Thanks, guys you did great. I gave more points to the second solution because THAT code snippet showed the LPAD in context so I knew exactly where to use it. While technically correct, the context helped me know where to put it. Thanks.