Mike Pala Mike Pala - 2 months ago 18
SQL Question

SQL Conditional Column selection

this might be a bit confusing so please bare with me ;) 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.

Answer

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;