时间戳

首页 > 解决方案 > how to properly split a tab delimited value into individual columns in ORACLE

sql - how to properly split a tab delimited value into individual columns in ORACLE

问题描述

I'm working with this query:

SELECT
    REGEXP_SUBSTR ('test1   test2   test3', '[^\t]+', 1, 1) field1,
    REGEXP_SUBSTR ('test1   test2   test3', '[^\t]+', 1, 2) field2,
    REGEXP_SUBSTR ('test1   test2   test3', '[^\t]+', 1, 3) field3
FROM DUAL

I'm looking for each field to have each value. this is a tab-delimited string. But I'm getting random outputs to it.

Looking for:

field1    field2    field3
test1     test2     test3

Getting:

field1    field2    field3
es        es        es

what am I missing?

thanks

标签： sqloracle

解决方案

[^\t]+将匹配不是反斜杠且不是字符的t字符。

所以

SELECT
    REGEXP_SUBSTR ('aaatbbb\ccc', '[^\t]+', 1, 1) field1,
    REGEXP_SUBSTR ('aaatbbb\ccc', '[^\t]+', 1, 2) field2,
    REGEXP_SUBSTR ('aaatbbb\ccc', '[^\t]+', 1, 3) field3
FROM DUAL

将输出：

字段1 | 字段2 | FIELD3
:----- | :----- | :-----
啊！bbb | ccc

将字符串拆分\为t

如果要将其拆分为制表符，则：

SELECT
    REGEXP_SUBSTR ('test1' || CHR(9) || 'test2' || CHR(9) || 'test3', '[^' || CHR(9) || ']+', 1, 1) field1,
    REGEXP_SUBSTR ('test1' || CHR(9) || 'test2' || CHR(9) || 'test3', '[^' || CHR(9) || ']+', 1, 2) field2,
    REGEXP_SUBSTR ('test1' || CHR(9) || 'test2' || CHR(9) || 'test3', '[^' || CHR(9) || ']+', 1, 3) field3
FROM DUAL

哪个输出：

字段1 | 字段2 | FIELD3
:----- | :----- | :-----
测试1 | 测试2 | 测试3

db<>在这里摆弄

推荐阅读