3 Replies Latest reply on Oct 6, 2016 12:03 PM by David Moyer

    Parsing Text String

    mikethecreative

      When importing data, I get the following text string:

       

      {"code":"102","jhrq":null,"rs":"[{\"code\":\"0\",\"content\":\"the server is busy, please try again later\",\"replaceContent\":null}]","orderID":"CARH16100046","message":null}

       

       

      I want to put the orderID (which in this case is CARH16100046) into a separate field. The orderID is always the same amount of characters and always comes after "orderID":"

       

      I have tried a mixture of calculations using Right, Position, Left, etc and can't seem to get what I want. How can I do this?

        • 1. Re: Parsing Text String
          philmodjunk

          Middle ( yourTexthere ; Position ( YourTextHere ; "orderID" ; 1 ; 1 )  + 10 ; 12 )

          • 2. Re: Parsing Text String
            fmpdude

            Assuming your order ID is always the same length, here's what I did:

             

            1. Since your text already had quote characters in it, I changed the "" to another character. I chose "|".

            (I'm not sure how FM handles escaping quotes.)

             

            -------

             

            2. Then, like Phil, but just a tad more generic, I did this search:

             

            Middle ( "{|code|:|102|,|jhrq|:null,|rs|:|[{\|code\|:\|0\|,\|content\|:\|the server is busy, please try again later\|,\|replaceContent\|:null}]|,|orderID|:|CARH16100046|,|message|:null}" ; Position ( "{|code|:|102|,|jhrq|:null,|rs|:|[{\|code\|:\|0\|,\|content\|:\|the server is busy, please try again later\|,\|replaceContent\|:null}]|,|orderID|:|CARH16100046|,|message|:null}" ; "ID|"; 1,1) + 5 ; 12 )

             

            Returns: CARH16100046

             

            Would be cleaner using a variable in a CF, for sure.

             

            ----

             

            If the OrderID isn't always the same length, then you would just need to add another Position function offset for the "12" above.

             

            HOPE THIS HELPS.

            • 3. Re: Parsing Text String
              David Moyer

              Hi,

              not that the above won't work, here's a custom function you can use in other, more dynamic, situations to parse text: