Oracle case in SQL
Hi,
I tried to use CASE in oracle SQL but was facing problem.
Anyhow if some can give command for below:
If returned column length is 3 then concatenation of 0 in the beginning of string, if length 4 then simply display it.
I tried to use CASE in oracle SQL but was facing problem.
Anyhow if some can give command for below:
If returned column length is 3 then concatenation of 0 in the beginning of string, if length 4 then simply display it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why use the case or decode at all? Just use lpad by itself.
drop table tab1 purge;
create table tab1(col1 varchar2(10));
insert into tab1 values('abc');
insert into tab1 values('abcd');
commit;
select lpad(col1,4,'o') from tab1;
Precisely what the lpad function is for. Do you have other criteria if the length is less than 3 or greater than four?
ASKER
Minimum 3 and max 4
Again: Why can you not just use lpad by itself?
If the test I posted in http:#a38421442 does not work, please add to it and explain where it does not work.
If the test I posted in http:#a38421442 does not work, please add to it and explain where it does not work.
Also again, that's precisely what lpad is for and, if the field can only be 3 or 4 characters in length, then the lpad(col,4,'o') that slightwv posted is all you need without a case or decode statement.
select case
when length(ename) = 4 then 0||ename
when length(ename) = 5 then ename
else 'xxxxx' end case_statement
from emp
/
CASE_STATEM
-----------
SMITH
ALLEN
0WARD
JONES
xxxxx
BLAKE
CLARK
SCOTT
0KING
xxxxx
ADAMS
JAMES
0FORD
xxxxx
14 rows selected.