在Oracle中将varchar拆分为单独的列

本文介绍了在Oracle中将varchar拆分为单独的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有点不高兴:我被要求接受从数据库中的特定字符串开始的注释，并将结果分成不同的列.

I'm in a bit of a pickle: I've been asked to take in comments starting with a specific string from a database, and separate the result into separate columns.

例如-如果返回的值是这样:

For example -- if a returned value is this:

COLUMN_ONE -------------------- 'D7ERROR username'

退货必须为:

COL_ONE COL_TWO -------------------- D7ERROR username

结构化结果集后，是否仅仅为了将字符串分成两部分就可以定义列?

Is it even possible to define columns once the result set has been structured just for the sake of splitting a string into two?

推荐答案

取决于数据的一致性-假设要在第一列和第二列中出现的内容之间用一个空格隔开:

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:

SELECT SUBSTR(t.column_one, 1, INSTR(t.column_one, ' ')-1) AS col_one, SUBSTR(t.column_one, INSTR(t.column_one, ' ')+1) AS col_two FROM YOUR_TABLE 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...

参考:

• SUBSTR
• INSTR

• SUBSTR
• INSTR