I have table like below in Oracle SQL Developer:
col1
-------
Regio Apo
Makreg One15
Regio Kawalisz
Makreg Podl
Makrego BB
AAA
And based on values in "col1" I need to create new column "col2". It should be binary columns:
- When value in "col1" starts with "M" return 1 in "col2"
- When value in "col1" starts with "R" return 0 in "col2"
- rather all values in "col1" start with M or R but just in case it starts with another letter give NULL
So as a result I need something like below:
col1 col2
-----------------------
Regio Apo | 0
Makreg One15 | 1
Regio Kawalisz | 0
Makreg Podl | 1
Makrego BB | 1
AAA | NULL
How can I do that in Oracle SQL Developer ?
>Solution :
CASE seems to be the most obvious:
SQL> with test (col1) as
2 (select 'Regio Apo' from dual union all
3 select 'Makreg One15' from dual union all
4 select 'Regio Kawalisz' from dual union all
5 select 'Makreg Podl' from dual union all
6 select 'Makrego BB' from dual union all
7 select 'AAA' from dual
8 )
9 select col1,
10 case when substr(col1, 1, 1) = 'M' then 1
11 when substr(col1, 1, 1) = 'R' then 0
12 else null
13 end cols
14 from test;
COL1 COLS
-------------- ----------
Regio Apo 0
Makreg One15 1
Regio Kawalisz 0
Makreg Podl 1
Makrego BB 1
AAA
6 rows selected.
SQL>