现在有一个这样的字符串:
789+456-239
我想得到一个这样的列表:
sign | num
+ 789
+ 456
- 239
这可能是一种方法,通过使用常用的拆分字符串方法,仅适合您处理符号的需要。
-- test case
with yourString(str) as
(
select '+789+456-239 ' str
from dual
)
-- query
SELECT regexp_substr(str, '[+-]', 1, level) sign,
regexp_substr(str, '[^+-]+', 1, level) num
FROM ( select case
when substr(str, 1, 1) in ('+','-') then str
-- I add a plus sign if the first char of the string is not a sign
else '+' || str
end as str
from yourString
)
CONNECT BY regexp_instr(str, '[+-]', 1, level ) > 0
这给出:
SIGN NUM
-------------- --------------
+ 789
+ 456
- 239
这里我假设如果在这个字符串的开头没有给出任何符号,第一个符号将是+
.
如果您需要处理多个字符串,则需要在表中标识一个 ID,这CONNECT BY
会变得有点复杂:
-- test case
with yourString(id, str) as
(
select 1, '+789+456-239' from dual union all
select 2, '789+456-239' from dual union all
select 3, '-789+456-239' from dual
)
-- query
SELECT id,
regexp_substr(str, '[+-]', 1, level) sign,
regexp_substr(str, '[^+-]+', 1, level) num
FROM ( select id,
case
when substr(str, 1, 1) in ('+','-') then str
-- I add a plus sign if the first char of the string is not a sign
else '+' || str
end as str
from yourString
)
CONNECT BY regexp_instr(str, '[+-]', 1, level ) > 0
and prior id = id
and prior sys_guid() is not null
结果:
ID SIGN NUM
---------- ----- --------------------------------------------
1 + 789
1 + 456
1 - 239
2 + 789
2 + 456
2 - 239
3 - 789
3 + 456
3 - 239
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句