How can i convert any number value to xxxx

  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,750
    Likes Received:
    382
    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,750
    Likes Received:
    382
    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
     
  6. satya.tanmay

    satya.tanmay Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Requirement
    Need result in 8digit,(comma not dot) then 2decimal digit i.e. 12345678,90 or 00001234,50 (if value is less than 8 digit then lpad with 0)

    I am using query as
    select LPAD(replace(to_number(to_char(1234.7,'S999999990D99')),'.',','),11,'0') from dual

    I am getting result as : 000001234,7 but i need result as 000001234,70
    i.e. 0 at the right is missing.

    How can get result as 000001234,70 and what should be my query.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,750
    Likes Received:
    382
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    select LPAD(replace(to_number(to_char(1234.7,'S999999990D90')),'.',','),11,'0') from dual