oracle数据库：用字符拆分字符串 [英] oracle database: split a string by characters
本文介绍了oracle数据库：用字符拆分字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要将一个字符串拆分成不同的记录，每个记录一个：
我有一个包含VARCHAR字段的查询，我需要拆分成多个字符。
select
MAPS.MAP_ID，
HARD_BIN_LINES.LINE，HARD_BIN_LINES.BINS
来自MAPS，HARD_BIN_LINES
其中MAPS.MAP_ID = 9595435和MAPS.MAP_ID = HARD_BIN_LINES.MAP_ID
order by HARD_BIN_LINES.MAP_ID，HARD_BIN_LINES.LINE
MAP_ID LINE BINS
9595435 1ÿÿÿÿÿÿÿÿÿÿÿþþþþÿÿÿÿÿÿÿÿÿÿÿ
9595435 2ÿÿÿÿÿÿÿþþþþÿÿÿÿÿÿÿÿ
9595435 3ÿÿÿÿÿþþ2þÿÿÿÿÿÿ
9595435 4ÿÿÿÿþþþÿÿÿÿ
9595435 5ÿÿÿþ2þÿÿÿ
9595435 6ÿÿþþÿÿ
9595435 7ÿÿþ2þÿÿ
9595435 8ÿþþÿþþÿ
9595435 9ÿ2þÿ
9595435 10þÿþÿ
9595435 11þþ
9595435 12üþ
9595435 13ü2þ
9595435 14þþ
9595435 15þÿ
9595435 16ÿþxxþÿ
9595435 17ÿþþÿ
9595435 18ÿÿþþÿÿ
9595435 19ÿÿþþÿÿ
9595435 20ÿÿÿþþÿÿÿ
9595435 21ÿÿÿÿþþþÿÿÿÿ
9595435 22 ÿÿÿÿÿ+þ2þÿÿÿÿÿÿ
9595435 23ÿÿÿÿÿÿÿþþþþÿÿÿÿÿÿÿÿ
9595435 24ÿÿÿÿÿÿÿÿÿÿÿþþþþÿÿÿÿÿÿÿÿÿÿÿ
我的目标是将BINS记录分成几个字母，像这样记录集
MAP_ID LINE LEVEL CHR BINCODE
- ----- + ------- + ------- + --- + --------
9595435 1 2ÿ255
9595435 1 3 ÿ255
9595435 1 4ÿ255
9595435 1 5ÿ255
9595435 1 6ÿ255
9595435 1 7ÿ255
9595435 1 8ÿ255
9595435 1 9ÿ255
9595435 1 10ÿ255
9595435 1 11ÿ255
9595435 1 12þ254
9595435 1 13þ254
9595435 1 14þ 254
9595435 1 15þ254
9595435 1 16ÿ255
9595435 1 17ÿ255
9595435 1 18ÿ255
9595435 1 19ÿ255
9595435 1 20ÿ255
9595435 1 21ÿ255
9595435 1 22ÿ255
9595435 1 23ÿ255
9595435 1 24ÿ255
9595435 1 25ÿ255
9595435 1 26ÿ255
------- + ------- + ------- + --- + --------
9595435 2 2ÿ255
9595435 2 3ÿ255
9595435 2 4ÿ255
9595435 2 5ÿ255
9595435 2 6ÿ255
9595435 2 7ÿ255
9595435 2 8þ254
9595435 2 9þ254
9595435 2 10 1
9595435 2 11 1
9595435 2 12 10
9595435 2 13 1
9595435 2 14 13
9595435 2 15 17
9595435 2 16 1
9595435 2 17þ254
9595435 2 18þ254
9595435 2 19ÿ255
9595435 2 20ÿ255
9595435 2 21ÿ255
9595435 2 22ÿ255
9595435 2 23ÿ255
9595435 2 24ÿ255
9595435 2 25ÿ255
9595435 2 26ÿ25
------- + ------- + ------- + --- + -------
（...）
如果我尝试解决使用CONNECT BY语句的问题我获得了很多重复的记录，我不明白为什么我的查询中没有什么问题：
，其中temp为（
select
MAPS.MAP_ID，
HARD_BIN_LINES.LINE，HARD_BIN_LINES.BINS
来自MAPS，HARD_BIN_LINES
其中MAPS.MAP_ID = 9595435和MAPS.MAP_ID = HARD_BIN_LINES.MAP_ID
order by HARD_BIN_LINES.MAP_ID，HARD_BIN_LINES.LINE
）
select
MAP_ID，LINE，LEVEL，
substr（BINS，level，1）as CHR，ASCII（substr（BINS，level，1））as BINCODE
从temp
按级别连接<= length（BINS）
解决方案
您可以尝试这样的：
select level，substr（'Stefano'，level，1）/ *从第level个字符开始的子字符串，1个字符日志* /
from dual
connect by level& = length（'Stefano'）/ *与字符串长度相同的行数* /
这将为起始字符串的每个字符构建一行，其中第N行包含由 substr 提取的第N个字符。
Being almost a newbie on Oracle database I kindly ask for your help.
I need to split a string into different records one for each caracter:
I have a query containing with a VARCHAR field that I need to split into several characters
select
MAPS.MAP_ID,
HARD_BIN_LINES.LINE, HARD_BIN_LINES.BINS
from MAPS, HARD_BIN_LINES
where MAPS.MAP_ID = 9595435 and MAPS.MAP_ID = HARD_BIN_LINES.MAP_ID
order by HARD_BIN_LINES.MAP_ID, HARD_BIN_LINES.LINE
MAP_ID LINE BINS
9595435 1 ÿÿÿÿÿÿÿÿÿÿÿþþþþÿÿÿÿÿÿÿÿÿÿÿ
9595435 2 ÿÿÿÿÿÿÿþþ þþÿÿÿÿÿÿÿÿ
9595435 3 ÿÿÿÿÿþþ2 þÿÿÿÿÿÿ
9595435 4 ÿÿÿÿþ þþÿÿÿÿ
9595435 5 ÿÿÿþ2 þÿÿÿ
9595435 6 ÿÿþ þÿÿ
9595435 7 ÿÿþ2 þÿÿ
9595435 8 ÿþþÿ þþÿ
9595435 9 ÿ2 þÿ
9595435 10 þÿ þÿ
9595435 11 þ þ
9595435 12 ü þ
9595435 13 ü2 þ
9595435 14 þ þ
9595435 15 þ ÿ
9595435 16 ÿþ xx þÿ
9595435 17 ÿþ þÿ
9595435 18 ÿÿþ þÿÿ
9595435 19 ÿÿþ þÿÿ
9595435 20 ÿÿÿþ þÿÿÿ
9595435 21 ÿÿÿÿþ þþÿÿÿÿ
9595435 22 ÿÿÿÿÿþ þ 2 þÿÿÿÿÿÿ
9595435 23 ÿÿÿÿÿÿÿþ þ þþ ÿÿÿÿÿÿÿÿ
9595435 24 ÿÿÿÿÿÿÿÿÿÿÿþþþþÿÿÿÿÿÿÿÿÿÿÿ
My goal is to split BINS record into several letter obtaing a recordset like this
MAP_ID LINE LEVEL CHR BINCODE
-------+-------+-------+---+--------
9595435 1 2 ÿ 255
9595435 1 3 ÿ 255
9595435 1 4 ÿ 255
9595435 1 5 ÿ 255
9595435 1 6 ÿ 255
9595435 1 7 ÿ 255
9595435 1 8 ÿ 255
9595435 1 9 ÿ 255
9595435 1 10 ÿ 255
9595435 1 11 ÿ 255
9595435 1 12 þ 254
9595435 1 13 þ 254
9595435 1 14 þ 254
9595435 1 15 þ 254
9595435 1 16 ÿ 255
9595435 1 17 ÿ 255
9595435 1 18 ÿ 255
9595435 1 19 ÿ 255
9595435 1 20 ÿ 255
9595435 1 21 ÿ 255
9595435 1 22 ÿ 255
9595435 1 23 ÿ 255
9595435 1 24 ÿ 255
9595435 1 25 ÿ 255
9595435 1 26 ÿ 255
-------+-------+-------+---+--------
9595435 2 2 ÿ 255
9595435 2 3 ÿ 255
9595435 2 4 ÿ 255
9595435 2 5 ÿ 255
9595435 2 6 ÿ 255
9595435 2 7 ÿ 255
9595435 2 8 þ 254
9595435 2 9 þ 254
9595435 2 10 1
9595435 2 11 1
9595435 2 12 10
9595435 2 13 1
9595435 2 14 13
9595435 2 15 17
9595435 2 16 1
9595435 2 17 þ 254
9595435 2 18 þ 254
9595435 2 19 ÿ 255
9595435 2 20 ÿ 255
9595435 2 21 ÿ 255
9595435 2 22 ÿ 255
9595435 2 23 ÿ 255
9595435 2 24 ÿ 255
9595435 2 25 ÿ 255
9595435 2 26 ÿ 25
-------+-------+-------+---+--------
(...)
If I try to solve the issue playing with "CONNECT BY" statement I obtain many duplicate records and I cannot understand why neither what's wrong on my query:
with temp as (
select
MAPS.MAP_ID,
HARD_BIN_LINES.LINE, HARD_BIN_LINES.BINS
from MAPS, HARD_BIN_LINES
where MAPS.MAP_ID = 9595435 and MAPS.MAP_ID = HARD_BIN_LINES.MAP_ID
order by HARD_BIN_LINES.MAP_ID, HARD_BIN_LINES.LINE
)
select
MAP_ID, LINE, LEVEL,
substr(BINS,level,1) as CHR, ASCII(substr(BINS,level,1)) as BINCODE
from temp
connect by level <= length(BINS)
解决方案
You can try with something like this:
select level, substr('Stefano', level, 1) /* a substring starting from level-th character, 1 character log */
from dual
connect by level <= length('Stefano') /* the same number of rows than the length of the string */
This will build one row for each character of the starting string, where the Nth row contains the Nth character, extracted by substr.
这篇关于oracle数据库：用字符拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
