取决于数据的一致性 - 假设单个空格是您想要在第一列和第二列中显示的内容之间的分隔符:
WITH TEST_DATA AS
(SELECT 'LOREM IPSUM' COLUMN_A FROM DUAL)
SELECT SUBSTR(t.COLUMN_A, 1, INSTR(t.COLUMN_A, ' ')-1) AS COLUMN_A,
SUBSTR(t.COLUMN_A, INSTR(t.COLUMN_A, ' ')+1) AS COLUMN_B
FROM test_data T;
您还可以将以下查询与 REGEX 一起使用:
WITH TEST_DATA AS
(SELECT 'LOREM IPSUM' COLUMN_A FROM DUAL)
SELECT REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 1) COLUMN_A,
REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 2) COLUMN_B
FROM test_data T;
Oracle 10g+ 具有正则表达式支持,可以根据您需要解决的情况提供更大的灵活性。它还有一个正则表达式子字符串方法......
EDIT:3 个单词拆分:
WITH TEST_DATA AS
(SELECT 'LOREM IPSUM DIMSUM' COLUMN_A FROM DUAL)
SELECT REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 1) COLUMN_A,
REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 1, 2) COLUMN_B,
REGEXP_SUBSTR(t.COLUMN_A, '[^ ]+', 2, 3) COLUMN_C
FROM test_data T;
参考:
- SUBSTR http://www.techonthenet.com/oracle/functions/substr.php
- INSTR http://www.techonthenet.com/oracle/functions/instr.php