divide string into multiple parts [message #669302] |
Sat, 14 April 2018 12:52 |
|
suji6281
Messages: 145 Registered: September 2014
|
Senior Member |
|
|
Hi All,
I want to divide string into multiple parts in sql query.
Example:
SELECT FLDVALUE FROM TABLE;
Assume output of above select statement is LPTQFS
And now I would like to divide it as LP, TQ, FS.
Please help me with solution.
Thank you.
Regards
Suji
|
|
|
|
|
|
Re: divide string into multiple parts [message #669306 is a reply to message #669302] |
Sat, 14 April 2018 14:39 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> with
2 data as (
3 select dbms_random.string('U',round(dbms_random.value(4,8))) str
4 from dual
5 connect by level <= 10
6 )
7 select str, rtrim(regexp_replace(str, '(..)','\1, '),', ') new_str
8 from data
9 /
STR NEW_STR
---------- ---------------
JPQXYDI JP, QX, YD, I
AOEYLIC AO, EY, LI, C
ZFPPVRW ZF, PP, VR, W
IWDLWK IW, DL, WK
JXPBTJ JX, PB, TJ
WGQXHBYA WG, QX, HB, YA
DOGKOYY DO, GK, OY, Y
JQET JQ, ET
BABYDF BA, BY, DF
UFZYQCX UF, ZY, QC, X
|
|
|
|
|
|
Re: divide string into multiple parts [message #669318 is a reply to message #669317] |
Sun, 15 April 2018 13:25 |
|
suji6281
Messages: 145 Registered: September 2014
|
Senior Member |
|
|
thanks Michel for your prompt response.
but i would need the output into multiple rows. Example as below:
STR NEW_STR
---------- ---------------
1 USNSWCO US
2 USNSWCO NS
3 USNSWCO WC
4 USNSWCO O
more over, if i count the rows here, it should be 4 rows.
select count(*) from data;
thank you.
Regards
suji
[Updated on: Sun, 15 April 2018 13:27] Report message to a moderator
|
|
|
|
|
Re: divide string into multiple parts [message #669321 is a reply to message #669318] |
Sun, 15 April 2018 13:58 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Just for fun - XQUERY solution:
with data as (
select dbms_random.string('U',round(dbms_random.value(4,8))) str
from dual
connect by level <= 10
)
select str,
i,
sub_str
from data,
xmltable(
'for $i in 1 to xs:integer(fn:ceiling(fn:string-length($s) div 2))
return fn:substring($s,2 *$i - 1,2)'
passing str as "s"
columns
sub_str varchar2(2) path '.',
i for ordinality
)
order by str,
i
/
STR I SUB_STR
---------- ---------- -------
BUZTVUEH 1 BU
BUZTVUEH 2 ZT
BUZTVUEH 3 VU
BUZTVUEH 4 EH
DDOKRV 1 DD
DDOKRV 2 OK
DDOKRV 3 RV
FWGIDRA 1 FW
FWGIDRA 2 GI
FWGIDRA 3 DR
FWGIDRA 4 A
JOBVGDA 1 JO
JOBVGDA 2 BV
JOBVGDA 3 GD
JOBVGDA 4 A
MLUTVS 1 ML
MLUTVS 2 UT
MLUTVS 3 VS
OBGVUO 1 OB
OBGVUO 2 GV
OBGVUO 3 UO
ROOP 1 RO
ROOP 2 OP
TTWLCH 1 TT
TTWLCH 2 WL
TTWLCH 3 CH
XJYPHR 1 XJ
XJYPHR 2 YP
XJYPHR 3 HR
ZXSGMHQ 1 ZX
ZXSGMHQ 2 SG
ZXSGMHQ 3 MH
ZXSGMHQ 4 Q
33 rows selected.
SQL>
SY.
|
|
|
|
Re: divide string into multiple parts [message #669327 is a reply to message #669321] |
Mon, 16 April 2018 01:46 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
May be numeric integer divide idiv simplifies solomon's XQUERY a little:
with data as
(select dbms_random.string('U',round(dbms_random.value(4,8))) str
from dual
connect by level <= 10)
select str,i,sub_str
from data,
xmltable
('for $i in 1 to (fn:string-length($s)+1) idiv 2
return fn:substring($s,2*$i - 1,2)'
passing str as "s"
columns
sub_str varchar2(2) path '.',
i for ordinality)
order by str,i;
[Updated on: Mon, 16 April 2018 01:47] Report message to a moderator
|
|
|