[SOLVED] SQL Conditional Column selection

SQL Conditional Column selection

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.

Solution

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;