SQL-将单列划分为多列 [英] SQL - Divide single column in multiple columns
问题描述
我有以下SQL问题:
如何使用SELECT命令将一列(内部文本)划分为两个带有拆分文本的单独列?
How to divide a column (text inside) using the SELECT command into two separate columns with split text?
我需要使用空格字符分隔文本数据. 我知道最好举一个例子来简化它.所以:
I need to separate the text-data, using the space character. I know it is better to put an example to make it easy. So:
SELECT COLUMN_A FROM TABLE1
输出:
COLUMN_A
-----------
LORE IPSUM
所需的输出:
COLUMN_A COLUMN_B
--------- ----------
LORE IPSUM
谢谢大家的帮助.
推荐答案
取决于数据的一致性-假设要在第一列和第二列中出现的内容之间用一个空格隔开:
Depends on the consistency of the data - assuming a single space is the separator between what you want to appear in column one vs two:
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中使用以下查询:
You can also use below query with 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 +具有正则表达式支持,根据您需要解决的情况提供更大的灵活性.它还有一个正则表达式子字符串方法...
Oracle 10g+ has regex support, allowing more flexibility depending on the situation you need to solve. It also has a regex substring method...
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
- INSTR
这篇关于SQL-将单列划分为多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!