4 Replies Latest reply: Feb 15, 2013 9:18 AM by Programmer Analyst RSS

    Substring

    Programmer Analyst
      Hello Gurus,
      I have a string which contains about 8 values that I need to break them up into individual pieces.
      the string looks somewhat like this..

      9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)

      Can you please help me fix this issue.
        • 1. Re: Substring
          Programmer Analyst
          any updates?
          • 2. Re: Substring
            Srini VEERAVALLI
            I see two different cases to handle 1) using ; and 2) using ^

            First I would start with ;

            Identify the position of ; and substring to that poistion, for the same query would be like

            SELECT
            SUBSTRING('9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)',1,
            CHARINDEX(';','9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)'))
            ---> This would return value 9.7^(Underweight); ---calling as-->A

            Then use the output ie. 9.7^(Underweight); to replace with '' to actual string for the same query would be like

            SELECT
            REPLACE('9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)',
            SUBSTRING('9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)',1,
            CHARINDEX(';','9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)')),'')
            ---> This would return value 4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)

            Identify the position of ; again from above output you get value as
            4 ft 4 in^(02/03/2004);----calling as--->B

            Do the same to get C as
            123 lb^(01/02/2004);

            D as 231/23^(04/05/2005)==>for this logic might be different

            Now its turn for split based on ^

            If helps mark and no more questions pls for the same :)

            I've Used SQL server db.
            • 3. Re: Substring
              Programmer Analyst
              Thanks for the response Srini.

              I forgot to mention, I am trying to split this in BMM. I know I can still try using substring in similar way but just letting you know so you can provide easier ideas if there are any with regard to this.
              Meanwhile, I will try to implement your suggestion.
              • 4. Re: Substring
                Programmer Analyst
                Works great...thanks Srini