1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How can i convert any number value to xxxx

Discussion in 'SQL PL/SQL' started by Jhon, Jun 11, 2019 at 11:24 PM.

  1. Jhon

    Jhon Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    VA
    Hi guys, i have a question for you. i want to convert number value to xxxx in sql.
    i mean
    select data_length from dba_tab_cols where owner='SCOTT' and table_name='TEST';
    it will return 9.
    but i want to return XXXXXXXX.
    i mean result-1
    if 7. then. XXXXXX
    if 5 then XXXX

    is it possible?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,682
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You haven't properly explained your question; provide more detail and better examples.
     
  3. Jhon

    Jhon Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    VA
    Suppose i have one table scott.test
    And there is column data_length and it is a number.
    When i am doing select colname,data_length from scott.test where name='tes';
    colname data_length
    id 5

    i need to convert this number value to XXXXX
    if data length 5 i want to replace 5 with XXXX
    suppose if it data_length returns 6 i want to replace 6 with XXXXX
    if data_length returns 9 i want to replace 9 with 'XXXXXXXX'
    any numbers i want to convert XXXXXX
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,682
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    My first question is 'Why?' But that doesn't apply to providing a solution.

    You should get very familiar with the rpad, lpad and length functions in Oracle as they will provide the results you ask for.


    Code (SQL):
    SCOTT @ orcl > SELECT data_length, rpad('X', data_length - 1, 'X') X, LENGTH(rpad('X', data_length -1,'X')) FROM test;

    DATA_LENGTH X                         LENGTH(RPAD('X',DATA_LENGTH-1,'X'))
    ----------- ---------------------------------------- -----------------------------------
             23 XXXXXXXXXXXXXXXXXXXXXX                              22

    SCOTT @ orcl >
     
  5. Jhon

    Jhon Starter

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    VA
    Thank you so much Bro