4 Replies Latest reply on Jan 20, 2020 8:18 AM by Mark Breeze

    Remedy 9 Smart Reporting - Freehand SQL - SubStr

    Mark Breeze
      Share This:

      Hi all, good day and happy Fridays!

       

      Wondering if there is an SQL wizard out there that could assist please.

       

      I'm looking to search for a value, after X before Y.

       

      I have the following, but having a real challenge in adding the final piece.

       

      SUBSTR(`SRM:REQUEST`.`Details`,44,(CHARINDEX('Last Name:',`SRM:REQUEST`.`Details`,0)-2))

       

      First Name: John Last Name: Jones Company: My Company

       

       

       

       

       

      In this example, I'm looking  to pull out the first name and last name into their own columns, there is text other side of the above in the same field.

       

       

      Thanks

       

      Mark

        • 1. Re: Remedy 9 Smart Reporting - Freehand SQL - SubStr
          Carl Wilson

          Hi Mark,

          have you tried to double nest the substring?

          Once you have returned the first value you can search in the resulting string for the next value.

          Something like the below (example only - not sure if this actually works but showing the concept):

           

          SUBSTR(SUBSTR(`SRM:REQUEST`.`Details`,44,(CHARINDEX('First Name:',`SRM:REQUEST`.`Details`,0)-2), `SRM:REQUEST`.`Details`,44,(CHARINDEX('Last Name:',`SRM:REQUEST`.`Details`,0)-2))

           

          Cheers

          Carl

          1 of 1 people found this helpful
          • 2. Re: Remedy 9 Smart Reporting - Freehand SQL - SubStr
            Mark Breeze

            Hi Carl,

             

            Thank you for having a nose.

             

            I have tried a number of flavours to get this to work including your idea, but I'm missing something as every time I enter a world of pain with - Parsing error near: <mismatched token:

            Thanks

             

            • 3. Re: Remedy 9 Smart Reporting - Freehand SQL - SubStr
              Sinisa Mikor

              Hi Mark,

               

              since you know lengths of  "First name: " and "Last name: " (12 and 11, respectively), it seems to me that you first need to determine three positions, because start_last can be determined from end_first, which need not be used as columns:

              1. beginning of first name as start_first = CHARINDEX("First name: ", `SRM:Request`.`Details`, 0) - 1 + 12
              2. end of first name as either
                1. end_first = CHARINDEX("Last name: ", `SRM:Request`.`Details`, 0) - 1 - 1, or
                2. end_first = CHARINDEX("Last name: "; `SRM:Request`.`Details`, start_first) - 1 - 1 to skip already examined part
              3. end of last name as end_last = CHARINDEX("Company: ", `SRM:Request`.`Details`, end_first + 11) - 1 - 1

              then use SUBSTR to display separate values in columns:

              1. first name as First name = SUBSTR(`SRM:Request`.`Details`, start_first, end_first)
              2. last name as Last name = SUBSTR(`SRM:Request`.`Details`, end_first + 11, end_last)
              2 of 2 people found this helpful
              • 4. Re: Remedy 9 Smart Reporting - Freehand SQL - SubStr
                Mark Breeze

                Thank you. I will give this a go.