SQL Conditional Column selection

Question

I'm running a simple query:

 Select "ID", "Original_ID" from table;

and that gets me this:

ID       |  Original_ID  
56917659 |  #56906230  Captain  
56781961 |  #56754164 Shipment Management   
56794542 |  #56620028 Team Member  
56655028 |  #50817965 Commercial Owner  
56258156 |  Process Expert                                         
55780912 |  Process Expert

I then can use this:

select "ID", substr("Original_ID",2,8)as "Original_ID" from table;  


ID       | Original_ID  
56917659 |  56906230  
56781961 |  56754164  
56794542 |  56620028  
56655028 |  50817965  
56258156 |  rocess E  
55780912 |  rocess E

what I need is a 3rd column "True_ID" which shows substr("Original_ID",2,8)as "True_ID" but ONLY if "Original_ID" starts with a '#' if not just show the value in "ID". I am working with dashDB.

rbr94 · Accepted Answer · 2016-09-30T09:14:42.213

1

Use CASE WHEN to decide if the first character of Original_ID is #:

select 
   ID
   , substr(Original_ID,2,8) as Original_ID
   , CASE WHEN substr(Original_ID, 1, 1) = '#' 
          THEN substr(Original_ID, 2, 8) 
          ELSE 'ID' END as True_ID
from table;

edited Sep 30 '16 at 09:14

answered Sep 30 '16 at 08:44

rbr94

2,227
3
23
39

thank you. I just had to make one small change: CASE WHEN substr(Original_ID, 1, 1) besides that worked like a charm, thank you – Mike Pala Sep 30 '16 at 08:53

score 1 · Answer 2 · answered Sep 30 '16 at 08:48

1

You can simply done with LEFT function in CASE:

SELECT
   ID,
   SUBSTR(Original_ID,2,8) as Original_ID,
   CASE WHEN LEFT(Original_ID, 1) = '#' 
      THEN SUBSTR(Original_ID, 2, 8) 
      ELSE ID
   END AS True_ID
FROM table;

answered Sep 30 '16 at 08:48

Shushil Bohara

5,556
2
15
32

SQL Conditional Column selection

2 Answers2