Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: Feel like makin' XML...but the ol' lady, she ain't buyin...

    Howdy, boys...

    Why isn't this working as expected?

    Code:
    DECLARE @Name varchar(20)
    SET @Name = 'AARON''S'
    
    SELECT '<NAME>' + RTRIM(REPLACE(REPLACE(@name, '''', '&apos;'), '&', '&amp;')) + '</NAME>' 
    
    output:
         <NAME>AARON&amp;apos;S</NAME>
    I expected the output to be <NAME>AARONapos;S</NAME>, but now whether there is an apostrophe OR an ampersand, I get both in the output.

    "Ahhhh"...you say, "that is UGLY, Paul..why would you want to do that anyway?" But that's not important right now, I just do (OK, OK, because my XML EXPLICIT is causing lots of
    Code:
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Server data (3046 bytes) exceeds host-file field length (0 bytes) for field (1).  Use prefix length, termination string, or a larger host-file field size.  Truncation cannot occur for BCP output files.
    errors. But I digress.

    Thinking that I am screwed, I played more, with this:
    Code:
    DECLARE @Name varchar(20)
    DECLARE @Name2 varchar(20)
    SET @Name = 'AARON''S'
    SET @Name2 = 'AARON&S'
    SELECT @Name
    SELECT @Name2
    
    SELECT '<NAME>' + RTRIM(REPLACE(REPLACE(@name, '&', '&amp;'), '''', '&apos;')) + '</NAME>' 
    SELECT '<NAME>' + RTRIM(REPLACE(REPLACE(@name2, '&', '&amp;'), '''', '&apos;')) + '</NAME>' 
    
    SELECT '<NAME>' + RTRIM(REPLACE(REPLACE(@name, '''', '&apos;'), '&', '&amp;')) + '</NAME>' 
    SELECT '<NAME>' + RTRIM(REPLACE(REPLACE(@name2, '''', '&apos;'), '&', '&amp;')) + '</NAME>'
    and ended up with THIS:
    Code:
                         
    -------------------- 
    AARON'S         
    AARON&S
       
    <NAME>AARON&apos;S</NAME>
    <NAME>AARON&amp;S</NAME>
    
    <NAME>AARON&amp;apos;S</NAME>
    <NAME>AARON&amp;S</NAME>
    So apparently the Old Girl just does not like replacing an apostrophe inside while she's replacing an ampersand on the outside. Can't say as I blame her, but still, it strikes me as odd. Maybe the ampersand prepares the Old Gal for the later apostrophe.


    Any thoughts? Should I call Redmond on the super secret bat phone?

    I will just use it as ampersand/apostrophe for now, but I don't like stuff like this in production because it always seems to result in a frantic call in the middle of the night at some later point.

    Really, I would prefer to get the XML EXPLICIT to work as I want it to, but until then, I guess my ugly code will suffice (at least for the data we have to work with here)...as long as I don't wrap an apostrophe in an ampersand.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Do your replaces the other way around.

    The problem is you're replacing double apostraphes with a string that contains an ampersand, and then you're applying a replace of ampersands with another string, so the ampersand that you inserted in your first replace then gets replaced again.

    If you replace the ampersand first, then replace the apostraphes you'll get better results.

    Code:
    DECLARE @Name varchar(20)
    SET @Name = 'AARON''S'
    Select RTRIM(REPLACE(Replace(@Name,'&','&amp;'), '''', '&apos;'))
    SET @Name = 'AARON&''S'
    Select RTRIM(REPLACE(Replace(@Name,'&','&amp;'), '''', '&apos;'))
    SET @Name = 'AARON''&S'
    Select RTRIM(REPLACE(Replace(@Name,'&','&amp;'), '''', '&apos;'))
    SET @Name = 'AARON&S'
    Select RTRIM(REPLACE(Replace(@Name,'&','&amp;'), '''', '&apos;'))

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Damn.

    Just Damn.

    It's there, plain as day when someone ELSE points it out. *LOL*

    Dummy me.

    Thanks nonetheless, for pointing out WHY I am a dummy (this time)
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •