• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

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.
0
baberamin
Asked:
baberamin
  • 2
  • 2
  • 2
  • +4
5 Solutions
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
select case when length('your_string') = 3 then 0||'your_string' when length('your_string') = 4 then 'your_string' end case_statement
from your_table;
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
for example for scott/tiger

 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.
0
 
IrogSintaCommented:
Would either of these work for you?  You wouldn't need to use CASE.
to_number(columnName,'0999')
substr('0000' | | columnName,-4,4)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this - we can use lpad right :

case when length(column_name) = 3 then lpad(column_name,4,'0')
else column_name end
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
0
 
Amitkumar PSr. ConsultantCommented:
check with decode also...

select
   decode(length(ename), 3, ('0'||ename), 4, ename, null)
from
   emp;

the default value would be null if the length is not 3 or 4.
0
 
slightwv (䄆 Netminder) Commented:
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;

Open in new window

0
 
awking00Commented:
Precisely what the lpad function is for. Do you have other criteria if the length is less than 3 or greater than four?
0
 
baberaminAuthor Commented:
Minimum 3 and max 4
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
awking00Commented:
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.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now