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?
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
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 >
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.