4 Replies Latest reply: Dec 14, 2016 8:13 AM by Stefan Wühl RSS

    Truncate Text

    juan prado

      Hey guys! I need your help again!

       

      I have a field with string with numbers and letters and  I need take only a part of the string

       

      for example:

       

      001024110672  SILVA VERONICA FERNANDA      01OT2407197405000

      001048562116  BARRERA SOFIA MORENA        02HO0203200805000

       

      I need to take the text in bold font, i try with this UPPER(replace(keepChar(NAME,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ '),' ','')) as NAME but i have 2 letters in the alphanumeric string.

       

      001024110672  SILVA VERONICA FERNANDA      01OT2407197405000

      001048562116  BARRERA SOFIA MORENA        02HO0203200805000


      How can i do for take only SILVA VERONICA FERNANDA and BARRERA SOFIA MORENA


      Thanks You!

        • Re: Truncate Text
          Sunny Talwar

          May be TextBetween()

           

          Trim(TextBetween(NAME, ' ', ' ')) as NAME

           

          UPDATE: May be this:

          Trim(Mid(NAME, FindOneOf(NAME, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'), FindOneOf(NAME, ' ', -1) - FindOneOf(NAME, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) as NAME;

           

          Capture.PNG

          • Re: Truncate Text
            Chanty 4u

            try this

            a:

            LOAD * INLINE [

                Field

                001024110672  SILVA VERONICA FERNANDA      01OT2407197405000

                001048562116  BARRERA SOFIA MORENA        02HO0203200805000

            ];

             

            Result:

            LOAD *,

            PurgeChar(Field,'0123456789') as new

            Resident a;

            prgr.PNG

            • Re: Truncate Text
              Deran Naidoo

              This works for me....

               

              data:
              LOAD
              trim(SubField(input,' ',2)) as new_input;
              LOAD * INLINE [
              input
              001024110672 SILVA VERONICA FERNANDA 01OT2407197405000
              001048562116 BARRERA SOFIA MORENA 02HO0203200805000
              ]
              ;

              • Re: Truncate Text
                Stefan Wühl

                Are the first part and the last part of fixed length? Looking at your sample, it could be 12 and 17 chars.

                 

                This could help in filtering your names:

                 

                Trim(Mid(NAME, 13, Len(NAME)-(12+17))) as NAME