Home » SQL & PL/SQL » SQL & PL/SQL » divide string into multiple parts
divide string into multiple parts [message #669302] Sat, 14 April 2018 12:52 Go to next message
suji6281
Messages: 134
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 #669303 is a reply to message #669302] Sat, 14 April 2018 12:58 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Have you looked at SUBSTR,
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SUBSTR.html
Re: divide string into multiple parts [message #669304 is a reply to message #669303] Sat, 14 April 2018 13:17 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thank you John.
SUBSTR should work but it should be generic. Since there will be either LPTQFS or LPFS or FSTQ or TQ or so on.... and it should divide into 2 char length. and any of 2 characters would come at any position in the string. Hence I am seeking for your suggestions. Thank you.
Re: divide string into multiple parts [message #669305 is a reply to message #669304] Sat, 14 April 2018 13:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What SQL have you tried so far? Have you looked at the various functions for finding strings and lengths? I'm not going to do your homework for you.
Re: divide string into multiple parts [message #669306 is a reply to message #669302] Sat, 14 April 2018 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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 #669315 is a reply to message #669306] Sun, 15 April 2018 09:08 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thank you Michel. Hope this logic works for me. Thank you very much.
Re: divide string into multiple parts [message #669316 is a reply to message #669315] Sun, 15 April 2018 11:14 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Michel,

Could you please help me that how to convert divided string into rows. I am looking for the output as below.

STR NEW_STR
---------- -----------
MNOPDS MN
OP
DS

Thank you.

Regards
Suji

Re: divide string into multiple parts [message #669317 is a reply to message #669316] Sun, 15 April 2018 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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
  8  '),'
  9  ') new_str
 10  from data
 11  /
STR        NEW_STR
---------- ---------------
USNSWCO    US
           NS
           WC
           O
EAPTQEB    EA
           PT
           QE
           B
RLUFT      RL
           UF
           T
TEKQUMX    TE
           KQ
           UM
           X
WFDX       WF
           DX
KFYMZ      KF
           YM
           Z
CLNGPIM    CL
           NG
           PI
           M
LYHNOIV    LY
           HN
           OI
           V
SRSDWB     SR
           SD
           WB
YTEWC      YT
           EW
           C
Please read How to use [code] tags and make your code easier to read.
Align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Re: divide string into multiple parts [message #669318 is a reply to message #669317] Sun, 15 April 2018 13:25 Go to previous messageGo to next message
suji6281
Messages: 134
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 #669319 is a reply to message #669318] Sun, 15 April 2018 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 15 April 2018 18:21
...
Please read How to use [code] tags and make your code easier to read.
Align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Re: divide string into multiple parts [message #669320 is a reply to message #669319] Sun, 15 April 2018 13:42 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Sorry Michel,

I am looking for output as below. Example as shown here:

    STR      NEW_STR
    -------  ---------------
1   USNSWCO   US
2   USNSWCO   NS
3   USNSWCO   WC
4   USNSWCO   O


Using your previous code, the output was as below:
    STR      NEW_STR
    -------  ---------------
1   USNSWCO   US
    USNSWCO   NS
    USNSWCO   WC
    USNSWCO   O





thank you.

Regards
suji
Re: divide string into multiple parts [message #669321 is a reply to message #669318] Sun, 15 April 2018 13:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #669322 is a reply to message #669321] Sun, 15 April 2018 14:14 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
thank you solomon.
Re: divide string into multiple parts [message #669327 is a reply to message #669321] Mon, 16 April 2018 01:46 Go to previous message
_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

Previous Topic: Problem in adding Html code in PL/SQL procedure
Next Topic: Need Help In Plsql Procedure
Goto Forum:
  


Current Time: Thu Mar 28 08:51:31 CDT 2024