Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Re: Elegant technique?

Expand Messages
  • Andrew Mills
    There is an XML datatype for this in SQL Server 2008 and I believe SQL Server 2005 (but not 100% of this). Thanks Andy From: SQLQueriesNoCode@yahoogroups.com
    Message 1 of 17 , Jun 1, 2011
    • 0 Attachment
      There is an XML datatype for this in SQL Server 2008 and I believe SQL
      Server 2005 (but not 100% of this).



      Thanks

      Andy



      From: SQLQueriesNoCode@yahoogroups.com
      [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Damhuis Anton
      Sent: Wednesday, June 01, 2011 10:11 AM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: [SQLQueriesNoCode] Re: Elegant technique?





      Hi

      Based on what Paul did, I added xml tags. I know somehow one can read xml
      tags from the later version of MS SQL.

      I have come up with this so far:

      --Begin SQL Code
      Create table Table1 (
      PO int,
      ID int,
      Comment varchar(50)
      )

      Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way
      XCITY Flagstaff XST');
      Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286
      XSHIP TO COUNTR');
      Insert into Table1 Values (1234567890, 3, 'Y US')

      SELECT
      W2.PO,
      SUBSTRING(
      Replace(
      (
      SELECT
      '' + T1.Comment
      FROM
      Table1 as T1
      WHERE
      T1.PO = W2.PO
      ORDER
      BY PO ASC, ID ASC
      FOR
      XML PATH('')
      ),
      'X', '</tag><tag>')
      ,7,1000)
      + '</tag>' as Comment
      FROM
      Table1 W2
      GROUP BY PO

      Drop table Table1
      --End SQL Code

      I still need to figure out how to read the xml tags into proper table
      columns. Maybe one of the other boffins can improve.

      Regards
      Anton Damhuis

      [cid:image001.jpg@01CC2076.7BA01420
      <mailto:image001.jpg%4001CC2076.7BA01420> ]

      Building 1, Inanda Greens Office Park, 54 Wierda Road West
      Wierde Valley, PO Box 786055, Sandton 2146
      e-mail: DamhuisAt@... <mailto:DamhuisAt%40ishltd.co.za>
      <mailto:DamhuisA@... <mailto:DamhuisA%40ishltd.co.za> >
      switchboard: +27 (11) 505 6000
      telephone: +27 (11) 505 6326

      www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
      Investment Solutions Limited is a licensed financial service provider. FAIS
      license number 711

      ________________________________
      Please note: This email and its contents are subject to the disclaimer as
      displayed at the following link:
      http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you
      not have web access, send an email to legalnotice@...
      <mailto:legalnotice%40ishltd.co.za> and a copy of this disclaimer will be
      sent to you.

      [Non-text portions of this message have been removed]





      [Non-text portions of this message have been removed]
    • Travis Truax
      Hi Paul, Yes, it s on a MSSQL2008 box. In the cursor-based routine, I ve just got a borderline illegible select statement filled with the following: SELECT
      Message 2 of 17 , Jun 1, 2011
      • 0 Attachment
        Hi Paul,
        Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got a borderline illegible select statement filled with the following:

        SELECT
        SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS vendor_order_no
        ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NBR%',SOP_COMMENT)+12,13) AS member_no
        ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS ship_to_name
        ......
        FROM erp_utilities.dbo.sop_order_header_comments_view
        WHERE ORDER_NUMBER = @sales_order_number
        AND PATINDEX('%XCLUB%',SOP_COMMENT) > 0


        Thanks, I hadn't realized that you could use the XML PATH clause to get around the ordered inline views issue.

        Travis-



        ----- Original Message -----

        From: "Paul Livengood" <p_livengood@...>
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Tuesday, May 31, 2011 4:35:25 PM
        Subject: Re: [SQLQueriesNoCode] Elegant technique?

        Assuming SQL 2005 or later then concatenating all the ocmment lines for each order is simple (see example at end), but grabing the substrings out between the X column identifiers will be difficult because there is no logic to it. X is a HORRIBLE deliminator because it can be used in normal words.

        Concatenate example
        SELECT w1.PO,
        (SELECT '' + w2.Comment
        FROM {Table Name} w2
        WHERE w1.PO = w2.PO
        ORDER BY PO, ID
        FOR XML PATH(''))
        FROM {Table Name} w1
        GROUP BY PO


        If you can supply more information on how you are spliting out the xColumn names I may be able to help make this set based instead of cursor based.

        HTH
        Paul

        From: Travis Truax <travis.truax@...>
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Tuesday, May 31, 2011 1:43 PM
        Subject: [SQLQueriesNoCode] Elegant technique?




        The order information we get from a certain .com retailer comes to us via EDI as a single merged string, chopped up into rows of data that are limited to something like 64 characters, encoded with field names identified by a preceding "X". Like so:

        po id comment
        1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
        1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
        1234567890 3 Y US

        It's really ugly, but I have to deal with it - it's not going to change.
        This is text processing that many folks would prefer to handle before it gets pushed into the DB, but we prefer to get it in the DB and then process it for other reasons.

        We need to concatenate all the comment lines for each order, sequenced by id, and then grab substrings out between the "X" column identifiers.

        I already have a kludgy cursor-based method for this, but I was wondering if there were any ways to do this that were more efficient. Like some way to concatenate all rows within a group or something similar. Even if that were possible, the rows must also be concatenated in proper sequence.
        I don't think SQL was really designed for this type of processing, but I thought I'd ask... sometimes you can do surprisingly well with the wrong tool for the job, and sometimes you just injure yourself. ;)

        Thanks,
        Travis-

        [Non-text portions of this message have been removed]




        [Non-text portions of this message have been removed]



        ------------------------------------

        Yahoo! Groups Links






        [Non-text portions of this message have been removed]
      • Travis Truax
        I have to plead ignorance of the XML functionality in SQL Server. I knew something was there, but I thought that if I left it alone, it might leave me alone.
        Message 3 of 17 , Jun 1, 2011
        • 0 Attachment
          I have to plead ignorance of the XML functionality in SQL Server. I knew "something" was there, but I thought that if I left it alone, it might leave me alone. :)

          Travis-

          ----- Original Message -----

          From: "Andrew Mills" <amills@...>
          To: SQLQueriesNoCode@yahoogroups.com
          Sent: Wednesday, June 1, 2011 9:20:34 AM
          Subject: RE: [SQLQueriesNoCode] Re: Elegant technique?

          There is an XML datatype for this in SQL Server 2008 and I believe SQL
          Server 2005 (but not 100% of this).



          Thanks

          Andy



          From: SQLQueriesNoCode@yahoogroups.com
          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Damhuis Anton
          Sent: Wednesday, June 01, 2011 10:11 AM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: [SQLQueriesNoCode] Re: Elegant technique?





          Hi

          Based on what Paul did, I added xml tags. I know somehow one can read xml
          tags from the later version of MS SQL.

          I have come up with this so far:

          --Begin SQL Code
          Create table Table1 (
          PO int,
          ID int,
          Comment varchar(50)
          )

          Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way
          XCITY Flagstaff XST');
          Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286
          XSHIP TO COUNTR');
          Insert into Table1 Values (1234567890, 3, 'Y US')

          SELECT
          W2.PO,
          SUBSTRING(
          Replace(
          (
          SELECT
          '' + T1.Comment
          FROM
          Table1 as T1
          WHERE
          T1.PO = W2.PO
          ORDER
          BY PO ASC, ID ASC
          FOR
          XML PATH('')
          ),
          'X', '</tag><tag>')
          ,7,1000)
          + '</tag>' as Comment
          FROM
          Table1 W2
          GROUP BY PO

          Drop table Table1
          --End SQL Code

          I still need to figure out how to read the xml tags into proper table
          columns. Maybe one of the other boffins can improve.

          Regards
          Anton Damhuis

          [cid:image001.jpg@01CC2076.7BA01420
          <mailto:image001.jpg%4001CC2076.7BA01420> ]

          Building 1, Inanda Greens Office Park, 54 Wierda Road West
          Wierde Valley, PO Box 786055, Sandton 2146
          e-mail: DamhuisAt@... <mailto:DamhuisAt%40ishltd.co.za>
          <mailto:DamhuisA@... <mailto:DamhuisA%40ishltd.co.za> >
          switchboard: +27 (11) 505 6000
          telephone: +27 (11) 505 6326

          www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
          Investment Solutions Limited is a licensed financial service provider. FAIS
          license number 711

          ________________________________
          Please note: This email and its contents are subject to the disclaimer as
          displayed at the following link:
          http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you
          not have web access, send an email to legalnotice@...
          <mailto:legalnotice%40ishltd.co.za> and a copy of this disclaimer will be
          sent to you.

          [Non-text portions of this message have been removed]





          [Non-text portions of this message have been removed]



          ------------------------------------

          Yahoo! Groups Links






          [Non-text portions of this message have been removed]
        • Damhuis Anton
          Ok this is what I worked it out as: --Begin SQL Code -- Drop table Table1 Create table Table1 ( PO int, ID int, Comment varchar(50) ) Insert into Table1 Values
          Message 4 of 17 , Jun 1, 2011
          • 0 Attachment
            Ok this is what I worked it out as:

            --Begin SQL Code
            -- Drop table Table1

            Create table Table1 (
            PO int,
            ID int,
            Comment varchar(50)
            )

            Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST');
            Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR');
            Insert into Table1 Values (1234567890, 3, 'Y US')

            Select
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[1]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column1,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[2]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column2,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[3]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column3,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[4]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column4,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[5]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column5,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[6]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column6,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[7]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column7,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[8]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column7,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[9]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column9,
            LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[10]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column10
            from
            (
            SELECT
            W2.PO,
            CAST(
            '<tags>' +
            SUBSTRING(
            Replace(
            (
            SELECT
            '' + T1.Comment
            FROM
            Table1 as T1
            WHERE
            T1.PO = W2.PO
            ORDER
            BY PO ASC, ID ASC
            FOR
            XML PATH('')
            ),
            'X', '</tag><tag>')
            ,7,1000)
            + '</tag></tags>'
            as XML) as Comment
            FROM
            Table1 W2
            GROUP BY PO
            ) as Table2

            Drop table Table1
            --End SQL Code

            Maybe needs to be optimised in some way? Suggestions?
            Would be interesting to know with all the casting going on, if it would be faster than a cursor.

            Regards
            Anton Damhuis

            [cid:image001.jpg@01CC207A.2F0816E0]

            Building 1, Inanda Greens Office Park, 54 Wierda Road West
            Wierde Valley, PO Box 786055, Sandton 2146
            e-mail: DamhuisAt@...<mailto:DamhuisA@...>
            switchboard: +27 (11) 505 6000
            telephone: +27 (11) 505 6326

            www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
            Investment Solutions Limited is a licensed financial service provider. FAIS license number 711




            ________________________________
            Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


            [Non-text portions of this message have been removed]
          • Damhuis Anton
            Oops sorry forgot to add: This linked helped me:
            Message 5 of 17 , Jun 1, 2011
            • 0 Attachment
              Oops sorry forgot to add:

              This linked helped me:
              http://beyondrelational.com/blogs/jacob/archive/2008/09/14/xquery-lab-34-how-to-retrieve-the-child-element-at-a-specified-position.aspx


              Regards
              Anton






              ________________________________
              Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


              [Non-text portions of this message have been removed]
            • Paul Livengood
              its not going to be pretty no matter what because we will ahve to hard code field names and lenghs. Can you supply all the possible field names and do they
              Message 6 of 17 , Jun 1, 2011
              • 0 Attachment
                its not going to be pretty no matter what because we will ahve to hard code field names and lenghs.
                Can you supply all the possible field names and do they always come in a specific order (or are they mixed up)?
                 

                From: Travis Truax <travis.truax@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Wednesday, June 1, 2011 8:23 AM
                Subject: Re: [SQLQueriesNoCode] Elegant technique?


                 

                Hi Paul,
                Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got a borderline illegible select statement filled with the following:

                SELECT
                SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS vendor_order_no
                ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NBR%',SOP_COMMENT)+12,13) AS member_no
                ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS ship_to_name
                ......
                FROM erp_utilities.dbo.sop_order_header_comments_view
                WHERE ORDER_NUMBER = @sales_order_number
                AND PATINDEX('%XCLUB%',SOP_COMMENT) > 0

                Thanks, I hadn't realized that you could use the XML PATH clause to get around the ordered inline views issue.

                Travis-

                ----- Original Message -----

                From: "Paul Livengood" <p_livengood@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Tuesday, May 31, 2011 4:35:25 PM
                Subject: Re: [SQLQueriesNoCode] Elegant technique?

                Assuming SQL 2005 or later then concatenating all the ocmment lines for each order is simple (see example at end), but grabing the substrings out between the X column identifiers will be difficult because there is no logic to it. X is a HORRIBLE deliminator because it can be used in normal words.

                Concatenate example
                SELECT w1.PO,
                (SELECT '' + w2.Comment
                FROM {Table Name} w2
                WHERE w1.PO = w2.PO
                ORDER BY PO, ID
                FOR XML PATH(''))
                FROM {Table Name} w1
                GROUP BY PO

                If you can supply more information on how you are spliting out the xColumn names I may be able to help make this set based instead of cursor based.

                HTH
                Paul

                From: Travis Truax <travis.truax@...>
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Tuesday, May 31, 2011 1:43 PM
                Subject: [SQLQueriesNoCode] Elegant technique?

                The order information we get from a certain .com retailer comes to us via EDI as a single merged string, chopped up into rows of data that are limited to something like 64 characters, encoded with field names identified by a preceding "X". Like so:

                po id comment
                1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
                1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
                1234567890 3 Y US

                It's really ugly, but I have to deal with it - it's not going to change.
                This is text processing that many folks would prefer to handle before it gets pushed into the DB, but we prefer to get it in the DB and then process it for other reasons.

                We need to concatenate all the comment lines for each order, sequenced by id, and then grab substrings out between the "X" column identifiers.

                I already have a kludgy cursor-based method for this, but I was wondering if there were any ways to do this that were more efficient. Like some way to concatenate all rows within a group or something similar. Even if that were possible, the rows must also be concatenated in proper sequence.
                I don't think SQL was really designed for this type of processing, but I thought I'd ask... sometimes you can do surprisingly well with the wrong tool for the job, and sometimes you just injure yourself. ;)

                Thanks,
                Travis-

                [Non-text portions of this message have been removed]

                [Non-text portions of this message have been removed]

                ------------------------------------

                Yahoo! Groups Links

                [Non-text portions of this message have been removed]




                [Non-text portions of this message have been removed]
              • Travis Truax
                Thanks Anton, Something else has come up so I can t work on merging any of the suggested solutions into my code at the moment, but I ll post what worked best
                Message 7 of 17 , Jun 1, 2011
                • 0 Attachment
                  Thanks Anton,
                  Something else has come up so I can't work on merging any of the suggested solutions into my code at the moment, but I'll post what worked best for me when I get back to it.

                  Travis-

                  ----- Original Message -----

                  From: "Damhuis Anton" <DamhuisA@...>
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Wednesday, June 1, 2011 9:37:23 AM
                  Subject: [SQLQueriesNoCode] Re: Elegant technique?

                  Ok this is what I worked it out as:

                  --Begin SQL Code
                  -- Drop table Table1

                  Create table Table1 (
                  PO int,
                  ID int,
                  Comment varchar(50)
                  )

                  Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST');
                  Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR');
                  Insert into Table1 Values (1234567890, 3, 'Y US')

                  Select
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[1]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column1,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[2]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column2,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[3]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column3,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[4]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column4,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[5]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column5,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[6]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column6,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[7]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column7,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[8]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column7,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[9]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column9,
                  LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[10]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column10
                  from
                  (
                  SELECT
                  W2.PO,
                  CAST(
                  '<tags>' +
                  SUBSTRING(
                  Replace(
                  (
                  SELECT
                  '' + T1.Comment
                  FROM
                  Table1 as T1
                  WHERE
                  T1.PO = W2.PO
                  ORDER
                  BY PO ASC, ID ASC
                  FOR
                  XML PATH('')
                  ),
                  'X', '</tag><tag>')
                  ,7,1000)
                  + '</tag></tags>'
                  as XML) as Comment
                  FROM
                  Table1 W2
                  GROUP BY PO
                  ) as Table2

                  Drop table Table1
                  --End SQL Code

                  Maybe needs to be optimised in some way? Suggestions?
                  Would be interesting to know with all the casting going on, if it would be faster than a cursor.

                  Regards
                  Anton Damhuis

                  [cid:image001.jpg@01CC207A.2F0816E0]

                  Building 1, Inanda Greens Office Park, 54 Wierda Road West
                  Wierde Valley, PO Box 786055, Sandton 2146
                  e-mail: DamhuisAt@...<mailto:DamhuisA@...>
                  switchboard: +27 (11) 505 6000
                  telephone: +27 (11) 505 6326

                  www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
                  Investment Solutions Limited is a licensed financial service provider. FAIS license number 711




                  ________________________________
                  Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


                  [Non-text portions of this message have been removed]



                  ------------------------------------

                  Yahoo! Groups Links






                  [Non-text portions of this message have been removed]
                • Travis Truax
                  This is the entire field list: (please don t bother coding more in detail, I was just looking for suggestions. I know you guys have stuff to do... ) Your
                  Message 8 of 17 , Jun 1, 2011
                  • 0 Attachment
                    This is the entire field list: (please don't bother coding more in detail, I was just looking for suggestions. I know you guys have stuff to do... ) Your inline view looks like it's going to do the job just fine.

                    XCLUB
                    XORDER
                    XMEMBER NBR
                    XMEMBER NAME
                    XSHIP TO NAME
                    XSHIP TO ADDR1
                    XSHIP TO ADDR2
                    XSHIP TO ADDR3
                    XSHIP_TO_CITY
                    XSHIP_TO_STATE
                    XSHIP_TO_ZIP-CODE
                    XSHIP_TO_PHONE
                    XSHIP_TO_COUNTRY

                    They do appear in order, however each is not always present (such as Address 2 & 3) and some can have slightly different formats (such as zip-code). I just handled that stuff with CASE statements in my query like:

                    ,CASE
                    --When the Address2 field exists - use it as the delimiter for the Address1 field.
                    WHEN PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0
                    THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR2%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14))
                    --When the Address3 field exists, without the Address2 field - use it as the delimiter for the Address1 field.
                    WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) = 0
                    THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14))
                    --When neither Address 2 or 3 fields exist - use the City field as the delimiter.
                    ELSE SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14))
                    END AS ship_to_addr1
                    ,CASE --If Address 2 field exists, but Address 3 field does not exist - use the City field as the delimiter for Address 2, otherwise do not include Address2 at all.
                    WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) = 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT)+14))
                    WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT)+14))
                    ELSE ''
                    END AS ship_to_addr2
                    ,CASE
                    WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT)+14))
                    ELSE ''
                    END AS ship_to_addr3


                    Travis-

                    ----- Original Message -----

                    From: "Paul Livengood" <p_livengood@...>
                    To: SQLQueriesNoCode@yahoogroups.com
                    Sent: Wednesday, June 1, 2011 9:40:27 AM
                    Subject: Re: [SQLQueriesNoCode] Elegant technique?

                    its not going to be pretty no matter what because we will ahve to hard code field names and lenghs.
                    Can you supply all the possible field names and do they always come in a specific order (or are they mixed up)?


                    From: Travis Truax <travis.truax@...>
                    To: SQLQueriesNoCode@yahoogroups.com
                    Sent: Wednesday, June 1, 2011 8:23 AM
                    Subject: Re: [SQLQueriesNoCode] Elegant technique?




                    Hi Paul,
                    Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got a borderline illegible select statement filled with the following:

                    SELECT
                    SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS vendor_order_no
                    ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NBR%',SOP_COMMENT)+12,13) AS member_no
                    ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS ship_to_name
                    ......
                    FROM erp_utilities.dbo.sop_order_header_comments_view
                    WHERE ORDER_NUMBER = @sales_order_number
                    AND PATINDEX('%XCLUB%',SOP_COMMENT) > 0

                    Thanks, I hadn't realized that you could use the XML PATH clause to get around the ordered inline views issue.

                    Travis-

                    ----- Original Message -----

                    From: "Paul Livengood" <p_livengood@...>
                    To: SQLQueriesNoCode@yahoogroups.com
                    Sent: Tuesday, May 31, 2011 4:35:25 PM
                    Subject: Re: [SQLQueriesNoCode] Elegant technique?

                    Assuming SQL 2005 or later then concatenating all the ocmment lines for each order is simple (see example at end), but grabing the substrings out between the X column identifiers will be difficult because there is no logic to it. X is a HORRIBLE deliminator because it can be used in normal words.

                    Concatenate example
                    SELECT w1.PO,
                    (SELECT '' + w2.Comment
                    FROM {Table Name} w2
                    WHERE w1.PO = w2.PO
                    ORDER BY PO, ID
                    FOR XML PATH(''))
                    FROM {Table Name} w1
                    GROUP BY PO

                    If you can supply more information on how you are spliting out the xColumn names I may be able to help make this set based instead of cursor based.

                    HTH
                    Paul

                    From: Travis Truax <travis.truax@...>
                    To: SQLQueriesNoCode@yahoogroups.com
                    Sent: Tuesday, May 31, 2011 1:43 PM
                    Subject: [SQLQueriesNoCode] Elegant technique?

                    The order information we get from a certain .com retailer comes to us via EDI as a single merged string, chopped up into rows of data that are limited to something like 64 characters, encoded with field names identified by a preceding "X". Like so:

                    po id comment
                    1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
                    1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
                    1234567890 3 Y US

                    It's really ugly, but I have to deal with it - it's not going to change.
                    This is text processing that many folks would prefer to handle before it gets pushed into the DB, but we prefer to get it in the DB and then process it for other reasons.

                    We need to concatenate all the comment lines for each order, sequenced by id, and then grab substrings out between the "X" column identifiers.

                    I already have a kludgy cursor-based method for this, but I was wondering if there were any ways to do this that were more efficient. Like some way to concatenate all rows within a group or something similar. Even if that were possible, the rows must also be concatenated in proper sequence.
                    I don't think SQL was really designed for this type of processing, but I thought I'd ask... sometimes you can do surprisingly well with the wrong tool for the job, and sometimes you just injure yourself. ;)

                    Thanks,
                    Travis-

                    [Non-text portions of this message have been removed]

                    [Non-text portions of this message have been removed]

                    ------------------------------------

                    Yahoo! Groups Links

                    [Non-text portions of this message have been removed]




                    [Non-text portions of this message have been removed]



                    ------------------------------------

                    Yahoo! Groups Links






                    [Non-text portions of this message have been removed]
                  • Arnie Rowland
                    This gets you a comma delimited Name/Value pair list. USE tempdb; GO INSERT INTO TestTable VALUES (1234567890, 1, XSHIP TO ADDR2 3500 Amiel Way XCITY
                    Message 9 of 17 , Jun 1, 2011
                    • 0 Attachment
                      This gets you a comma delimited Name/Value pair list.

                      USE tempdb;
                      GO

                      INSERT INTO TestTable
                      VALUES
                      (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff
                      XST'),
                      (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR'),
                      (1234567890, 3, 'Y US');

                      SELECT
                      PO,
                      replace( Comment, ' X', ',') AS 'Comment'
                      FROM ( SELECT
                      t1.PO,
                      Comment = substring((SELECT ( '' + Comment )
                      FROM TestTable t2
                      WHERE t1.PO = t2.PO
                      ORDER BY
                      PO,
                      ID
                      FOR XML PATH( '' )
                      ), 2, 1000 ) FROM TestTable t1
                      GROUP BY PO
                      ) t3

                      Then you can use a split function to separate the Name/Value pairs.
                      See:
                      http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Strin
                      gArrayInput

                      You can then use REPLACE() to remove the column names, or just suppress
                      them in the report (labels/invoices). IF you choose to use REPLACE(), I
                      suggest putting the known Column Name values in a table, and cycle
                      through the table. Much better than 'hard coding'...


                      Regards,

                      Arnie Rowland, MVP (SQL Server)

                      "You cannot do a kindness too soon because you never know how soon it
                      will be too late."
                      -Ralph Waldo Emerson




                      -----Original Message-----
                      From: SQLQueriesNoCode@yahoogroups.com
                      [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                      Sent: Wednesday, June 01, 2011 8:08 AM
                      To: SQLQueriesNoCode@yahoogroups.com
                      Subject: Re: [SQLQueriesNoCode] Elegant technique?

                      This is the entire field list: (please don't bother coding more in
                      detail, I was just looking for suggestions. I know you guys have stuff
                      to do... ) Your inline view looks like it's going to do the job just
                      fine.

                      XCLUB
                      XORDER
                      XMEMBER NBR
                      XMEMBER NAME
                      XSHIP TO NAME
                      XSHIP TO ADDR1
                      XSHIP TO ADDR2
                      XSHIP TO ADDR3
                      XSHIP_TO_CITY
                      XSHIP_TO_STATE
                      XSHIP_TO_ZIP-CODE
                      XSHIP_TO_PHONE
                      XSHIP_TO_COUNTRY

                      They do appear in order, however each is not always present (such as
                      Address 2 & 3) and some can have slightly different formats (such as
                      zip-code). I just handled that stuff with CASE statements in my query
                      like:

                      ,CASE
                      --When the Address2 field exists - use it as the delimiter for the
                      Address1 field.
                      WHEN PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0 THEN
                      SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                      ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR2%',SOP_COMMENT)-(PATIND
                      EX('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) --When the Address3 field
                      exists, without the Address2 field - use it as the delimiter for the
                      Address1 field.
                      WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP
                      TO ADDR2%',SOP_COMMENT) = 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP
                      TO
                      ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATIND
                      EX('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) --When neither Address 2 or 3
                      fields exist - use the City field as the delimiter.
                      ELSE SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                      ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                      X('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) END AS ship_to_addr1 ,CASE --If
                      Address 2 field exists, but Address 3 field does not exist - use the
                      City field as the delimiter for Address 2, otherwise do not include
                      Address2 at all.
                      WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) = 0 AND PATINDEX('%XSHIP
                      TO ADDR2%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP
                      TO
                      ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                      X('%XSHIP TO ADDR2%',SOP_COMMENT)+14)) WHEN PATINDEX('%XSHIP TO
                      ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) >
                      0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                      ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATIND
                      EX('%XSHIP TO ADDR2%',SOP_COMMENT)+14)) ELSE ''
                      END AS ship_to_addr2
                      ,CASE
                      WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 THEN
                      SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                      ADDR3%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                      X('%XSHIP TO ADDR3%',SOP_COMMENT)+14)) ELSE ''
                      END AS ship_to_addr3


                      Travis-

                      ----- Original Message -----

                      From: "Paul Livengood" <p_livengood@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Wednesday, June 1, 2011 9:40:27 AM
                      Subject: Re: [SQLQueriesNoCode] Elegant technique?

                      its not going to be pretty no matter what because we will ahve to hard
                      code field names and lenghs.
                      Can you supply all the possible field names and do they always come in a
                      specific order (or are they mixed up)?


                      From: Travis Truax <travis.truax@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Wednesday, June 1, 2011 8:23 AM
                      Subject: Re: [SQLQueriesNoCode] Elegant technique?




                      Hi Paul,
                      Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got
                      a borderline illegible select statement filled with the following:

                      SELECT
                      SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS
                      vendor_order_no ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER
                      NBR%',SOP_COMMENT)+12,13) AS member_no
                      ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER
                      NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO
                      NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS
                      member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                      NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO
                      ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS
                      ship_to_name ......
                      FROM erp_utilities.dbo.sop_order_header_comments_view
                      WHERE ORDER_NUMBER = @sales_order_number AND
                      PATINDEX('%XCLUB%',SOP_COMMENT) > 0

                      Thanks, I hadn't realized that you could use the XML PATH clause to get
                      around the ordered inline views issue.

                      Travis-

                      ----- Original Message -----

                      From: "Paul Livengood" <p_livengood@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Tuesday, May 31, 2011 4:35:25 PM
                      Subject: Re: [SQLQueriesNoCode] Elegant technique?

                      Assuming SQL 2005 or later then concatenating all the ocmment lines for
                      each order is simple (see example at end), but grabing the substrings
                      out between the X column identifiers will be difficult because there is
                      no logic to it. X is a HORRIBLE deliminator because it can be used in
                      normal words.

                      Concatenate example
                      SELECT w1.PO,
                      (SELECT '' + w2.Comment
                      FROM {Table Name} w2
                      WHERE w1.PO = w2.PO
                      ORDER BY PO, ID
                      FOR XML PATH(''))
                      FROM {Table Name} w1
                      GROUP BY PO

                      If you can supply more information on how you are spliting out the
                      xColumn names I may be able to help make this set based instead of
                      cursor based.

                      HTH
                      Paul

                      From: Travis Truax <travis.truax@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Tuesday, May 31, 2011 1:43 PM
                      Subject: [SQLQueriesNoCode] Elegant technique?

                      The order information we get from a certain .com retailer comes to us
                      via EDI as a single merged string, chopped up into rows of data that are
                      limited to something like 64 characters, encoded with field names
                      identified by a preceding "X". Like so:

                      po id comment
                      1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
                      1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
                      1234567890 3 Y US

                      It's really ugly, but I have to deal with it - it's not going to change.

                      This is text processing that many folks would prefer to handle before it
                      gets pushed into the DB, but we prefer to get it in the DB and then
                      process it for other reasons.

                      We need to concatenate all the comment lines for each order, sequenced
                      by id, and then grab substrings out between the "X" column identifiers.

                      I already have a kludgy cursor-based method for this, but I was
                      wondering if there were any ways to do this that were more efficient.
                      Like some way to concatenate all rows within a group or something
                      similar. Even if that were possible, the rows must also be concatenated
                      in proper sequence.
                      I don't think SQL was really designed for this type of processing, but I
                      thought I'd ask... sometimes you can do surprisingly well with the wrong
                      tool for the job, and sometimes you just injure yourself. ;)

                      Thanks,
                      Travis-

                      [Non-text portions of this message have been removed]

                      [Non-text portions of this message have been removed]

                      ------------------------------------

                      Yahoo! Groups Links

                      [Non-text portions of this message have been removed]




                      [Non-text portions of this message have been removed]



                      ------------------------------------

                      Yahoo! Groups Links






                      [Non-text portions of this message have been removed]



                      ------------------------------------

                      Yahoo! Groups Links







                      [Non-text portions of this message have been removed]
                    • Travis Truax
                      Thanks Arnie, I m actually just parsing the data they ve made sausage out of and inserting it into a shipping table to integrate with Fedex & UPS software
                      Message 10 of 17 , Jun 1, 2011
                      • 0 Attachment
                        Thanks Arnie,
                        I'm actually just parsing the data they've made sausage out of and inserting it into a shipping table to integrate with Fedex & UPS software among other things, so the field names aren't needed.

                        Travis-

                        ----- Original Message -----

                        From: "Arnie Rowland" <arnie@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Wednesday, June 1, 2011 11:23:02 AM
                        Subject: RE: [SQLQueriesNoCode] Elegant technique?

                        This gets you a comma delimited Name/Value pair list.

                        USE tempdb;
                        GO

                        INSERT INTO TestTable
                        VALUES
                        (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff
                        XST'),
                        (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR'),
                        (1234567890, 3, 'Y US');

                        SELECT
                        PO,
                        replace( Comment, ' X', ',') AS 'Comment'
                        FROM ( SELECT
                        t1.PO,
                        Comment = substring((SELECT ( '' + Comment )
                        FROM TestTable t2
                        WHERE t1.PO = t2.PO
                        ORDER BY
                        PO,
                        ID
                        FOR XML PATH( '' )
                        ), 2, 1000 ) FROM TestTable t1
                        GROUP BY PO
                        ) t3

                        Then you can use a split function to separate the Name/Value pairs.
                        See:
                        http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Strin
                        gArrayInput

                        You can then use REPLACE() to remove the column names, or just suppress
                        them in the report (labels/invoices). IF you choose to use REPLACE(), I
                        suggest putting the known Column Name values in a table, and cycle
                        through the table. Much better than 'hard coding'...


                        Regards,

                        Arnie Rowland, MVP (SQL Server)

                        "You cannot do a kindness too soon because you never know how soon it
                        will be too late."
                        -Ralph Waldo Emerson




                        -----Original Message-----
                        From: SQLQueriesNoCode@yahoogroups.com
                        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                        Sent: Wednesday, June 01, 2011 8:08 AM
                        To: SQLQueriesNoCode@yahoogroups.com
                        Subject: Re: [SQLQueriesNoCode] Elegant technique?

                        This is the entire field list: (please don't bother coding more in
                        detail, I was just looking for suggestions. I know you guys have stuff
                        to do... ) Your inline view looks like it's going to do the job just
                        fine.

                        XCLUB
                        XORDER
                        XMEMBER NBR
                        XMEMBER NAME
                        XSHIP TO NAME
                        XSHIP TO ADDR1
                        XSHIP TO ADDR2
                        XSHIP TO ADDR3
                        XSHIP_TO_CITY
                        XSHIP_TO_STATE
                        XSHIP_TO_ZIP-CODE
                        XSHIP_TO_PHONE
                        XSHIP_TO_COUNTRY

                        They do appear in order, however each is not always present (such as
                        Address 2 & 3) and some can have slightly different formats (such as
                        zip-code). I just handled that stuff with CASE statements in my query
                        like:

                        ,CASE
                        --When the Address2 field exists - use it as the delimiter for the
                        Address1 field.
                        WHEN PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0 THEN
                        SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR2%',SOP_COMMENT)-(PATIND
                        EX('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) --When the Address3 field
                        exists, without the Address2 field - use it as the delimiter for the
                        Address1 field.
                        WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP
                        TO ADDR2%',SOP_COMMENT) = 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP
                        TO
                        ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATIND
                        EX('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) --When neither Address 2 or 3
                        fields exist - use the City field as the delimiter.
                        ELSE SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                        X('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) END AS ship_to_addr1 ,CASE --If
                        Address 2 field exists, but Address 3 field does not exist - use the
                        City field as the delimiter for Address 2, otherwise do not include
                        Address2 at all.
                        WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) = 0 AND PATINDEX('%XSHIP
                        TO ADDR2%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP
                        TO
                        ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                        X('%XSHIP TO ADDR2%',SOP_COMMENT)+14)) WHEN PATINDEX('%XSHIP TO
                        ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) >
                        0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATIND
                        EX('%XSHIP TO ADDR2%',SOP_COMMENT)+14)) ELSE ''
                        END AS ship_to_addr2
                        ,CASE
                        WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 THEN
                        SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        ADDR3%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                        X('%XSHIP TO ADDR3%',SOP_COMMENT)+14)) ELSE ''
                        END AS ship_to_addr3


                        Travis-

                        ----- Original Message -----

                        From: "Paul Livengood" <p_livengood@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Wednesday, June 1, 2011 9:40:27 AM
                        Subject: Re: [SQLQueriesNoCode] Elegant technique?

                        its not going to be pretty no matter what because we will ahve to hard
                        code field names and lenghs.
                        Can you supply all the possible field names and do they always come in a
                        specific order (or are they mixed up)?


                        From: Travis Truax <travis.truax@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Wednesday, June 1, 2011 8:23 AM
                        Subject: Re: [SQLQueriesNoCode] Elegant technique?




                        Hi Paul,
                        Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got
                        a borderline illegible select statement filled with the following:

                        SELECT
                        SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS
                        vendor_order_no ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER
                        NBR%',SOP_COMMENT)+12,13) AS member_no
                        ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER
                        NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO
                        NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS
                        member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO
                        ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS
                        ship_to_name ......
                        FROM erp_utilities.dbo.sop_order_header_comments_view
                        WHERE ORDER_NUMBER = @sales_order_number AND
                        PATINDEX('%XCLUB%',SOP_COMMENT) > 0

                        Thanks, I hadn't realized that you could use the XML PATH clause to get
                        around the ordered inline views issue.

                        Travis-

                        ----- Original Message -----

                        From: "Paul Livengood" <p_livengood@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Tuesday, May 31, 2011 4:35:25 PM
                        Subject: Re: [SQLQueriesNoCode] Elegant technique?

                        Assuming SQL 2005 or later then concatenating all the ocmment lines for
                        each order is simple (see example at end), but grabing the substrings
                        out between the X column identifiers will be difficult because there is
                        no logic to it. X is a HORRIBLE deliminator because it can be used in
                        normal words.

                        Concatenate example
                        SELECT w1.PO,
                        (SELECT '' + w2.Comment
                        FROM {Table Name} w2
                        WHERE w1.PO = w2.PO
                        ORDER BY PO, ID
                        FOR XML PATH(''))
                        FROM {Table Name} w1
                        GROUP BY PO

                        If you can supply more information on how you are spliting out the
                        xColumn names I may be able to help make this set based instead of
                        cursor based.

                        HTH
                        Paul

                        From: Travis Truax <travis.truax@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Tuesday, May 31, 2011 1:43 PM
                        Subject: [SQLQueriesNoCode] Elegant technique?

                        The order information we get from a certain .com retailer comes to us
                        via EDI as a single merged string, chopped up into rows of data that are
                        limited to something like 64 characters, encoded with field names
                        identified by a preceding "X". Like so:

                        po id comment
                        1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
                        1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
                        1234567890 3 Y US

                        It's really ugly, but I have to deal with it - it's not going to change.

                        This is text processing that many folks would prefer to handle before it
                        gets pushed into the DB, but we prefer to get it in the DB and then
                        process it for other reasons.

                        We need to concatenate all the comment lines for each order, sequenced
                        by id, and then grab substrings out between the "X" column identifiers.

                        I already have a kludgy cursor-based method for this, but I was
                        wondering if there were any ways to do this that were more efficient.
                        Like some way to concatenate all rows within a group or something
                        similar. Even if that were possible, the rows must also be concatenated
                        in proper sequence.
                        I don't think SQL was really designed for this type of processing, but I
                        thought I'd ask... sometimes you can do surprisingly well with the wrong
                        tool for the job, and sometimes you just injure yourself. ;)

                        Thanks,
                        Travis-

                        [Non-text portions of this message have been removed]

                        [Non-text portions of this message have been removed]

                        ------------------------------------

                        Yahoo! Groups Links

                        [Non-text portions of this message have been removed]




                        [Non-text portions of this message have been removed]



                        ------------------------------------

                        Yahoo! Groups Links






                        [Non-text portions of this message have been removed]



                        ------------------------------------

                        Yahoo! Groups Links







                        [Non-text portions of this message have been removed]



                        ------------------------------------

                        Yahoo! Groups Links






                        [Non-text portions of this message have been removed]
                      • Arnie Rowland
                        Travis, I think that you will find the below to be a far more efficient (and faster) method to concatenate the strings and then parse out the values. The query
                        Message 11 of 17 , Jun 1, 2011
                        • 0 Attachment
                          Travis,

                          I think that you will find the below to be a far more efficient (and
                          faster) method to concatenate the strings and then parse out the values.
                          The query returns a EAV (Name-Value pair) table. By creating a ColNames
                          table, you will not have to hard code in order to parse, and you can
                          easily add additional column names when they show up.

                          Query Results:

                          NAME VALUE
                          PURCHASE ORDER 1234567890
                          SHIP TO ADDR2 3500 Amiel Way
                          CITY Flagstaff
                          SHIP TO ZIP-CODE 44286
                          SHIP TO COUNTRY US

                          Regards,

                          Arnie Rowland, MVP (SQL Server)

                          "You cannot do a kindness too soon because you never know how soon it
                          will be too late."
                          -Ralph Waldo Emerson


                          USE tempdb;
                          GO

                          -- This FUNCTION parses the concatenated string
                          CREATE FUNCTION dbo.Split
                          ( @Delimiter varchar(5),
                          @List varchar(8000)
                          )
                          RETURNS @TableOfValues table
                          ( RowID smallint IDENTITY(1,1),
                          [Value] varchar(50)
                          )
                          AS
                          BEGIN
                          DECLARE @LenString int
                          WHILE len( @List ) > 0
                          BEGIN
                          SELECT @LenString =
                          (CASE charindex( @Delimiter, @List )
                          WHEN 0 THEN len( @List )
                          ELSE ( charindex( @Delimiter, @List ) -1 )
                          END
                          )
                          INSERT INTO @TableOfValues
                          SELECT substring( @List, 1, @LenString )
                          SELECT @List =
                          (CASE ( len( @List ) - @LenString )
                          WHEN 0 THEN ''
                          ELSE right( @List, len( @List ) - @LenString - 1 )
                          END
                          )
                          END
                          RETURN
                          END;
                          GO

                          -- This is your test data table
                          CREATE TABLE TestTable
                          ( PO int,
                          ID int,
                          Comment varchar(50)
                          );
                          GO

                          -- The ColumnNames table precludes hard-coding
                          CREATE TABLE ColNames
                          ( ColName varchar(50));
                          GO

                          -- The ColumnNames table precludes hard-coding, based on your supplied
                          data
                          INSERT INTO ColNames
                          VALUES
                          ( 'PURCHASE ORDER' ),
                          ( 'CITY' ),
                          ( 'CLUB' ),
                          ( 'ORDER' ),
                          ( 'MEMBER NBR' ),
                          ( 'MEMBER NAME' ),
                          ( 'SHIP TO NAME' ),
                          ( 'SHIP TO ADDR1' ),
                          ( 'SHIP TO ADDR2' ),
                          ( 'SHIP TO ADDR3' ),
                          ( 'SHIP TO CITY' ),
                          ( 'SHIP TO STATE' ),
                          ( 'SHIP TO ZIP-CODE' ),
                          ( 'SHIP TO PHONE' ),
                          ( 'SHIP TO COUNTRY' )

                          -- Supplied data to concatenate and parse
                          INSERT INTO TestTable
                          VALUES
                          (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff
                          XST'),
                          (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR'),
                          (1234567890, 3, 'Y US');

                          -- Query to create Name/Value pairs from the string
                          SELECT
                          c.ColName AS 'NAME',
                          replace( t4.VALUE, (c.ColName + ' '), '' ) AS 'VALUE'
                          FROM dbo.Split( ',', (SELECT
                          replace( ('Purchase Order ' + cast(PO AS
                          varchar(20)) + ',' + Comment), ' X', ',') AS 'Comment'
                          FROM ( SELECT
                          t1.PO,
                          Comment = substring((SELECT ( '' +
                          Comment )
                          FROM TestTable t2
                          WHERE t1.PO = t2.PO
                          ORDER BY
                          PO,
                          ID
                          FOR XML PATH( '' )
                          ), 2, 1000 ) FROM
                          TestTable t1
                          GROUP BY PO
                          ) t3 )) t4
                          JOIN ColNames c
                          ON t4.Value LIKE (c.ColName + '%');

                          -- Clean Up
                          DROP TABLE TestTable;
                          DROP TABLE ColNames;
                          DROP FUNCTION dbo.SPLIT;


                          [Non-text portions of this message have been removed]
                        • Arnie Rowland
                          (Darn funky formatting...) Travis, I think that you will find the below to be a far more efficient (and faster) method to concatenate the strings and then
                          Message 12 of 17 , Jun 1, 2011
                          • 0 Attachment
                            (Darn funky formatting...)

                            Travis,

                            I think that you will find the below to be a far more efficient (and
                            faster) method to concatenate the strings and then parse out the values.
                            The query returns a EAV (Name-Value pair) table. By creating a ColNames
                            table, you will not have to hard code in order to parse, and you can
                            easily add additional column names when they show up.

                            Query Results:

                            NAME VALUE
                            PURCHASE ORDER 1234567890
                            SHIP TO ADDR2 3500 Amiel Way
                            CITY Flagstaff
                            SHIP TO ZIP-CODE 44286
                            SHIP TO COUNTRY US


                            Regards,

                            Arnie Rowland, MVP (SQL Server)

                            "You cannot do a kindness too soon because you never know how soon it
                            will be too late."
                            -Ralph Waldo Emerson


                            USE tempdb;
                            GO

                            -- This FUNCTION parses the concatenated string
                            CREATE FUNCTION dbo.Split
                            ( @Delimiter varchar(5),
                            @List varchar(8000)
                            )
                            RETURNS @TableOfValues table
                            ( RowID smallint IDENTITY(1,1),
                            [Value] varchar(50)
                            )
                            AS
                            BEGIN
                            DECLARE @LenString int
                            WHILE len( @List ) > 0
                            BEGIN
                            SELECT @LenString =
                            (CASE charindex( @Delimiter, @List )
                            WHEN 0 THEN len( @List )
                            ELSE ( charindex( @Delimiter, @List ) -1 )
                            END
                            )
                            INSERT INTO @TableOfValues
                            SELECT substring( @List, 1, @LenString )
                            SELECT @List =
                            (CASE ( len( @List ) - @LenString )
                            WHEN 0 THEN ''
                            ELSE right( @List, len( @List ) - @LenString - 1 )
                            END
                            )
                            END
                            RETURN
                            END;
                            GO

                            -- This is your test data table
                            CREATE TABLE TestTable
                            ( PO int,
                            ID int,
                            Comment varchar(50)
                            );
                            GO

                            -- The ColumnNames table precludes hard-coding
                            CREATE TABLE ColNames
                            ( ColName varchar(50));
                            GO

                            -- The ColumnNames table precludes hard-coding, based on your supplied
                            data
                            INSERT INTO ColNames
                            VALUES
                            ( 'PURCHASE ORDER' ),
                            ( 'CITY' ),
                            ( 'CLUB' ),
                            ( 'ORDER' ),
                            ( 'MEMBER NBR' ),
                            ( 'MEMBER NAME' ),
                            ( 'SHIP TO NAME' ),
                            ( 'SHIP TO ADDR1' ),
                            ( 'SHIP TO ADDR2' ),
                            ( 'SHIP TO ADDR3' ),
                            ( 'SHIP TO CITY' ),
                            ( 'SHIP TO STATE' ),
                            ( 'SHIP TO ZIP-CODE' ),
                            ( 'SHIP TO PHONE' ),
                            ( 'SHIP TO COUNTRY' )

                            -- Supplied data to concatenate and parse
                            INSERT INTO TestTable
                            VALUES
                            (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff
                            XST'),
                            (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR'),
                            (1234567890, 3, 'Y US');

                            -- Query to create Name/Value pairs from the string
                            SELECT
                            c.ColName AS 'NAME',
                            replace( t4.VALUE, (c.ColName + ' '), '' ) AS 'VALUE'
                            FROM dbo.Split( ',', (SELECT
                            replace( ('Purchase Order ' + cast(PO AS
                            varchar(20)) + ',' + Comment), ' X', ',') AS 'Comment'
                            FROM ( SELECT
                            t1.PO,
                            Comment = substring((SELECT ( '' +
                            Comment )
                            FROM TestTable t2
                            WHERE t1.PO = t2.PO
                            ORDER BY
                            PO,
                            ID
                            FOR XML PATH( '' )
                            ), 2, 1000 ) FROM
                            TestTable t1
                            GROUP BY PO
                            ) t3 )) t4
                            JOIN ColNames c
                            ON t4.Value LIKE (c.ColName + '%');

                            -- Clean Up
                            DROP TABLE TestTable;
                            DROP TABLE ColNames;
                            DROP FUNCTION dbo.SPLIT;


                            [Non-text portions of this message have been removed]
                          Your message has been successfully submitted and would be delivered to recipients shortly.