5 Replies Latest reply: May 15, 2014 9:57 AM by Marco Viehl RSS

    Extract only the first line of a multiline DB field

    Marco Viehl

      Hi,

      one of my customers use Monarch to get data from his MS SQL Database. He uses one view to get some fields from the database including a filed that has maybe more than one line. Monarch then displays all the lines in one line in one text field. That's fine.

       

      Is it possible to use a formula to extract only the first line of this data. That means all the content before the first "crlf" in the data.

       

      I was thinking about lsplit(field;2;crlf;1) or something.

       

      Any idea?

       

      Thanks in advance before any help.

       

      Best regards,

      Marco.

        • Extract only the first line of a multiline DB field
          Grant Perkins

          Hi Marco,

           

          Your idea is oprobably sound - I assume you already know what, if any, line break identifier is retained in the SQL database.

           

          You will need to use the ASCii character number to identify the separator.

           

          This thread, although not exactly what you are seeking to do (almost the opposite in fact!) may provide useful information.

           

          [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000099#000002"]http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000099#000002[/url]

           

          An alternative would be to select a certain number of characters from the beginning of the field but that might not be completely consistent in the database.

           

          However, at the risk of providing a different way of doing exactly what the SPLIT functions do, you could deal with a variable by identifying the position in the string at which a certain character or string of characters appears and use that value as the basis for the LENGTH of a character string to be extracted, starting from the first character in the field.

           

          How much of these solutions can be achieved using just one or two functions will depend on which Monarch version your client is using. (And what is in the database field!)

           

          I hope this helps. If you need some more input to the problem just let us know.

           

           

          Grant

           

          [size="1"][ February 08, 2006, 11:27 AM: Message edited by: Mike Urbonas ][/size]

          • Extract only the first line of a multiline DB field
            Marco Viehl

            Thank you Grant,

            unfortunately i was not able to split the field in Monarch (7), but it was good to know that it should work and my idea was right.

             

            Now i solved the problem on the MSSQL Database Level using a view with an additional field built by "substring(<fieldname>,1,charindex(char(13),<fieldname>))".

             

            Checking chr(13) in Monarch did not work.

             

            But again: thank you for your help.

             

            Best Regards,

            Marco.

            • Extract only the first line of a multiline DB field
              Gareth Horton

              Marco

               

              Although you have found a solution, the new TextLine function in Monarch 8 would be exactly what you need.

               

              Gareth

               

              Originally posted by MarcoViehl:

              Hi,

              one of my customers use Monarch to get data from his MS SQL Database. He uses one view to get some fields from the database including a filed that has maybe more than one line. Monarch then displays all the lines in one line in one text field. That's fine.

               

              Is it possible to use a formula to extract only the first line of this data. That means all the content before the first "crlf" in the data.

               

              I was thinking about lsplit(field;2;crlf;1) or something.

               

              Any idea?

               

              Thanks in advance before any help.

               

              Best regards,

              Marco. /b[/quote]

              • Extract only the first line of a multiline DB field
                Grant Perkins

                Hi Marco,

                 

                I must confess I did wonder if it would work as documented - what you are doing may not be the exactly the same as the original discussion referenced.

                 

                Did you try for chr(10) as well?

                 

                I think Gareth has suggested a good option. I did not mention it myself as I was not sure whether the German release for V8 was available. However, if you have solved the problem anyway  - a clever solution I think - that is the most important result.

                 

                Best regards,

                 

                 

                Grant

                • Extract only the first line of a multiline DB field
                  Marco Viehl

                  Hi Grant,

                   

                  yes i tried CHR(10) as well, but no success. I think this is because MS SQL Server delivers this Data different than it would be in plain text.

                   

                  So that is why i decided to change the database view on database level.

                   

                  Unfortunately i have no german Version 8 of Monarch, because there isn't one so far. So maybe i can check Gareth' solution later.

                   

                  Thanks again for your help.

                   

                  Best Regards,

                  Marco.