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

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 ?

CodePudding user response：

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>

CodePudding user response：

I need to create new column "col2".

Add a virtual column to the table:

ALTER TABLE table_name
  ADD (
    col2 NUMBER(1,0)
         GENERATED ALWAYS AS (
           CASE SUBSTR(col1, 1, 1)
           WHEN 'M' THEN 1
           WHEN 'R' THEN 0
           ELSE NULL
           END
         )
);

Which, for the sample data:

CREATE TABLE table_name (col1) AS
  SELECT 'Regio Apo'      FROM DUAL UNION ALL
  SELECT 'Makreg One15'   FROM DUAL UNION ALL
  SELECT 'Regio Kawalisz' FROM DUAL UNION ALL
  SELECT 'Makreg Podl'    FROM DUAL UNION ALL
  SELECT 'Makrego BB'     FROM DUAL UNION ALL
  SELECT 'AAA'            FROM DUAL;

After adding the column, then:

SELECT * FROM table_name;

Outputs:

COL1 COL2

Regio Apo 0

Makreg One15 1

Regio Kawalisz 0

Makreg Podl 1

Makrego BB 1

AAA

COL1	COL2
Regio Apo	0
Makreg One15	1
Regio Kawalisz	0
Makreg Podl	1
Makrego BB	1
AAA

db<>fiddle here