3 Replies Latest reply: Mar 25, 2013 10:37 AM by Brian Wilkinson RSS

    Search a string to pull out data.

      I am importing a string from an excel file and i need to clean up one of the colums and make it two columns.

       

      The first thing i need to do was get rid of the first two charcaters whihc was no big deal.

       

      The next part is where i am having the issue i am trying to pull out a chunk of the string to create another field.

       

      My issue is the chunk of data for 90% of the records is the last 10 bytes of the string so thats pretty simply but for the other 10% of the records the chunk is in the first 9 bytes of the string.

       

      ZZXXXXXXXXX XXXXXXXXXXX XXXXX XXXXXXXXX XXXXXX XXXXX 9999999999

      ZZ999999999XXXXXXXXX XXXXXXXXXXX XXXXX XXXXXXXXX XXXXXX XXXXX

       

      So to get rid of the first two bytes i just did this which worked fine to get rid of the two bytes i dont want.

       

           right(trim(@2),len(trim(@2))-2) as String1

       

      but to pull out the data for the new field I was simply doing a right function but i need to to pull either the right most 10 characters or the left most 9 characters depending on where the number happens to be and how long it is

       

           right(trim(@2),10) as String2