将一行拆分为多行（固定宽度）

发布于 2024-12-12 09:44:45 字数 366 浏览 0 评论 0原文

一行传入记录有多个子记录串联。每个子记录的长度为 9 个字符。我每行有8个这样的子记录。所以每行是（8x9=72 个字符）。有没有办法可以将 1 条记录分成 8 条记录？

输入

123456789123456789123456789123456789123456789123456789123456789123456789

输出

我知道我可以用 8 个子查询来做到这一点并将它们联合起来..有更好的方法吗？

原文

One row of incoming record has multiple sub-records concatenated. Each sub record is 9 characters long. I have 8 such sub-records in each row. So each row is (8x9=72 char). Is there a way i can split 1 record into 8 records here?

Input

123456789123456789123456789123456789123456789123456789123456789123456789

Output

I know i can do this with 8 sub queries and union them .. is there a better way?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乖乖兔^ω^ 2024-12-19 09:44:45

或者这样怎么样：

SELECT substr(t1.astr,n*9+1,9) 
FROM t1,
(SELECT ROWNUM-1 n FROM dual CONNECT BY LEVEL <= 8) t2

表 t1 包含您的字符串，表 t2 是通过将其交叉连接到 t1 表生成的从 0 到 7 的顺序数字列表，我们可以使用它来分割字符串列。

or how about this:

SELECT substr(t1.astr,n*9+1,9) 
FROM t1,
(SELECT ROWNUM-1 n FROM dual CONNECT BY LEVEL <= 8) t2

table t1 contains your strings, table t2 is a generated list of sequential numbers from 0 to 7 by cross joining it to the t1 table we can use it to cut up the string column.

回复收藏 0 原文

内心荒芜 2024-12-19 09:44:45

我见过人们使用仅包含值 1 到 8 的辅助表，然后与该表连接。您可以使用该值来对记录进行子字符串化。

像“注意：查询未经测试”之类的东西

SELECT SUBSTRING(long_val, eight.value * 9, 9)
FROM mytable, eight

，但我希望这个想法能够得到理解。

I've seen people use a secondary table containing only the values 1 through 8, then joining with that table. You can use the value to substring your record.

Something like

SELECT SUBSTRING(long_val, eight.value * 9, 9)
FROM mytable, eight

Note: Query untested, but I hope the idea gets across.

回复收藏 0 原文

枫林﹌晚霞¤ 2024-12-19 09:44:45

如果可以预见的是，每行总是会分成固定数量的行，那么类似以下的事情应该是可能的：

select  
    b.iterator,
    substr(a.mystring,b.iterator*9-8,9) as split
from

(select '123456789123456789123456789123456789123456789123456789123456789123456789' as mystring from dual) a,
(select 1 as iterator from dual union
 select 2 as iterator from dual union
 select 3 as iterator from dual union
 select 4 as iterator from dual union
 select 5 as iterator from dual union
 select 6 as iterator from dual union
 select 7 as iterator from dual union
 select 8 as iterator from dual) b

编辑：凯文的迭代器比我的强力版本更简单、更好。 b 子查询应该是 (SELECT ROWNUM as iterator FROM Dual CONNECT BY LEVEL <= 8)

If it is predictable that each line will always split into a fixed number of lines, something like the following should be possible:

select  
    b.iterator,
    substr(a.mystring,b.iterator*9-8,9) as split
from

(select '123456789123456789123456789123456789123456789123456789123456789123456789' as mystring from dual) a,
(select 1 as iterator from dual union
 select 2 as iterator from dual union
 select 3 as iterator from dual union
 select 4 as iterator from dual union
 select 5 as iterator from dual union
 select 6 as iterator from dual union
 select 7 as iterator from dual union
 select 8 as iterator from dual) b

EDIT: Kevin's iterator was simpler and better than my brute force version. The b subquery shoud be (SELECT ROWNUM as iterator FROM dual CONNECT BY LEVEL <= 8)

回复收藏 0 原文

~没有更多了~