PL/SQL equivalent of T-SQL REPLICATE statement

Posted on 2007-10-18
Last Modified: 2013-12-19
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.    
Question by:safair
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    lpad('*', 5, '*' )
    LVL 31

    Accepted Solution

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

    Author Comment

    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.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This video shows how to recover a database from a user managed backup
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now