Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to create binary column based on starts character in other column in Oracle SQL Developer?

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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>
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading