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

4501RE: [SQLQueriesNoCode] RE: UPDATE question

Expand Messages
  • Arnie Rowland
    Apr 6, 2010
    • 0 Attachment
      Use the REPLACE() function, replacing ' & ' with ' & '
      (Note the spaces before and after.)

      Perhaps something like:

      SELECT replace(' & ', (SELECT Vendor_or_Mfg + ', ' as 'data()'
      FROM [Table_4(Vendor_or_Mfg)] t4
      WHERE t4.F_documentID=t.F_documentID
      FOR XML PATH('')) Vendor_or_Mfg
      ), ' & ' )


      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 John Warner
      Sent: Tuesday, April 06, 2010 9:38 AM
      To: Arnie
      Subject: [SQLQueriesNoCode] RE: UPDATE question

      First thanks again to all for the suggestions on the UPDATE query
      yesterday. Works like a charm. But another small problem has popped up
      and I think this is in SQL Server.

      One of the fields I hit with my subqueries in the UPDATE is a Name
      (vendors) and some of the names have the '&' sign in them such as John
      & Warner, Inc

      The SELECT:

      SELECT Vendor_or_Mfg + ', ' as 'data()'
      FROM [Table_4(Vendor_or_Mfg)] t4
      WHERE t4.F_documentID=t.F_documentID
      FOR XML PATH('')) Vendor_or_Mfg

      I get John & Warner, Inc

      I need the & but not the amp. I know the XML is doing this, anyway to
      make it 'stop' or a simple way without a cursor to pass through the rows
      returned and replace & with just &? Set preferred to procedural
      process is what I'm after.

      John Warner


      Yahoo! Groups Links

      Disclaimer - April 6, 2010
      This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
      This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

      [Non-text portions of this message have been removed]
    • Show all 18 messages in this topic