Thread: I would like to lpad the first 10 numbers

I have a table which holds both negative and positive integers for a particular column and the column can accomodate any no. of integers.

I would like to lpad the first 10 numbers.
I can get my expected result for the positive integer.
But for the negative integer, I will get my result has '****-12345', if used lpad(dno,10,'*')
which is not the expected output.
The o/p should be similar to '-****12345'.
How can I achieve it.

Thanks in advance.
Question asked by visitor karthick

Hi, you can use a case expression in this problem,

/* Oracle DB */

select case when dno < 0 then '-' || lpad(dno,9,'*')
else lpad(dno,10,'*')
end
from dual

On other RDBMS I don't know if case expression are supported but I think that case expressions are part of the SQL92 standar.

HTH

Try this dear it will work fine:-

select replace(dno,substr(dno,2,10),'**********') from dept

Try this dear it will work fine:-

select replace(dno,substr(dno,2,10),'**********') from dept