ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Statement for spliting value

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Statement for spliting value

    Hi guys... I need your help on a topic.. I have a DB2 file with a field of 300 char with some information related to transfers.. for this field I need to retrieve a substring and currently I don't figure out to find exactly the position..
    the statement is this:

    SELECT RESULT FROM TRLOG
    This is why I have in RESULT field:
    RESULT='sftp> cd home/RMTUSR/ sftp> lcd /home/LCLUSR sftp> put CLIENT_CLIENT Report_clients.txt sftp> quit Transferred: sent 13664'

    Can you help me please with this.. I builded this until now:
    SELECT substring( substring(RESULT,POSITION('put ' in
    RESULT)+4 ),10) FROM TRLOG
    but.. I need to split more to get report name, namely Report_clients.txt:
    CLIENT_CLIENT Report_clients.txt sftp> quit Transferred: sent 13664


  • #2
    Fixed it guys, I succeded !!
    I did several searches about some fixed words, which never will be changed, like 'put'.. and after this I searched the blanks which again will remain the same.
    find below, maybe someone will need something similar:
    SELECT substring(
    (substring( substring(RESULT,POSITION('put ' in
    SFTPRESULT)+4 ), POSITION(' ' in
    substring(RESULT,POSITION('put ' in RESULT)+ 4))+1)), 1,
    locate_in_string(
    (substring( substring(RESULT,POSITION('put ' in
    SFTRESULT)+4 ), POSITION(' ' in
    substring(SFTPRESULT,POSITION('put ' in SFTPRESULT)+ 4))+1))
    , ' ')) FROM TRLOG

    Comment


    • #3
      You really don't say what the requirements are so I have to guess that you want to extract out "Report_clients.txt" since that's in bold.
      And your SQL statement makes my brain hurt. I wouldn't want to be the next developer that has to maintain that statement.
      Seems like LIKE and a Length and a Locate and Substring would have worked, if my guess is right.

      Ringer

      Comment


      • #4
        yeah, this was the substring needed, Report_clients.txt ... sorry that I missed this, I put in bold but I forgot to mentioned that this is the information to be retrieved.. sorry.. I was rushed..
        if you have some ideas to optimize please go ahead, happy to do a better interogation.. it will be only used to fed an output file for downstream.. so, basically we'll run with a query, nothing special

        Comment


        • #5
          Code:
          // 'sftp> cd home/RMTUSR/ sftp> lcd /home/LCLUSR sftp> put CLIENT_CLIENT [B]Report_clients.txt [/B]sftp> quit Transferred: sent 13664'
          select substr(RESULT , locate(' put CLIENT_CLIENT ' , RESULT) + 19, 
                 locate(' sftp> quit' , RESULT) -                         
                 locate(' put CLIENT_CLIENT ' , RESULT) - 19)             
          from TRLOG 
          where RESULT like 'sftp% put % sftp> quit%'
          Ringer

          Comment


          • #6
            Thanks CRinger400, it's more simple.. but unfortunately it's not aplicable to me... because this example was a test one, the example I gave, all the values may vary, so, CLIENT_CLIENT it is only a test value, but here I can have AAAAAAAAAAAAA_AAAAAA, I mean with a longer length, idem for Report_clients.txt .. I can have BBBBBBBBBBB.txt or CCCCCCCCCCC.csv, with longer length, with different extension, the fixed parts are only the prompt from QSH followed by the commands which are standards and always the same, namely sftp> cd followed by XXXXXX (variable length), sftp > put SSSSSS SSSS etc.. so, basically for this reason I have such many substrings and locate and I'm referring only to the fixed parts in my SQL statement, it's my mistake because I didn't specified from the begining these details, but I fixed it with this solution quickly, probably can be optimize but it is ok and I will keep it

            Comment

            Working...
            X