我有点不高兴:我被要求接受从数据库中的特定字符串开始的注释,并将结果分成不同的列.
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 tOracle 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
更多推荐
在Oracle中将varchar拆分为单独的列
发布评论