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 >