如何在Oracle SQL Developer中基于其他列中的起始字符创建二进制列

Question

我在Oracle SQL Developer中有如下表：

col1
-------
Regio Apo
Makreg One15
Regio Kawalisz
Makreg Podl
Makrego BB
AAA

并且基于"；col1"；我需要创建新的列"；col2"；。它应该是二进制列：

• 当"；col1"；以"；M〃；"返回1"；col2">
• 当值为"0"时；col1"；以"；R〃；在"0"中返回0；col2">
• 而是"0"中的所有值；col1"；以M或R开头，但如果它以另一个字母开头，则为NULL

因此，我需要以下内容：

col1             col2
-----------------------
Regio Apo      | 0
Makreg One15   | 1
Regio Kawalisz | 0
Makreg Podl    | 1
Makrego BB     | 1
AAA            | NULL

如何在Oracle SQL Developer中做到这一点？

Answer 1

CASE似乎是最明显的：

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>

Answer 2

我需要创建新列"col2"；。

向表中添加一个虚拟列：

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
)
);

对于样本数据：

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;

添加柱后，则：

SELECT * FROM table_name;

输出：

COL1	COL2
Regio Apo	0
Makreg One15	1
Regio Kawaliz	0
Makreg播客	1
Makrego BB	1
AAA