Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Undocumented "feature"

    Dig this....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE ItemInformation([Description] varchar(80))
    GO
    
    INSERT INTO ItemInformation([Description])
    SELECT 'CHOCOLATE CHIP‚' UNION ALL
    SELECT '‚COOKIES‚' UNION ALL
    SELECT '‚CROISSANTS *PLAIN*‚' UNION ALL
    SELECT '‚DONUTS‚' UNION ALL
    SELECT '‚DONUTS *DOZEN*‚' UNION ALL
    SELECT '‚MUFFINS‚' UNION ALL
    SELECT '‚BAGELS‚' UNION ALL
    SELECT '‚ROLLS‚' UNION ALL
    SELECT '‚CUPCAKES‚' UNION ALL
    SELECT '‚CRISPIES‚' UNION ALL
    SELECT '‚DANISH/SWEET ROLLS‚' UNION ALL
    SELECT '‚FUDGE BROWNIES‚' UNION ALL
    SELECT '‚PUFF PASTRIES/ECCLES‚' UNION ALL
    SELECT '‚STICKY BUNS‚' UNION ALL
    SELECT '‚TURNOVERS‚' UNION ALL
    SELECT '‚BLACK & WHITE COOKIES‚' UNION ALL
    SELECT '‚LINZER TARTS‚' UNION ALL
    SELECT '‚SCONES/BISCUITS‚' UNION ALL
    SELECT '‚SCUFFINS‚' UNION ALL
    SELECT '‚SINFULL BITS‚'
    GO
    
    SELECT * FROM ItemInformation
    GO
    
    UPDATE ItemInformation
       SET [Description] = REPLACE([Description],',','')
    GO
    
    SELECT [Description], LEN([Description]) FROM ItemInformation
    GO
    
    SELECT REPLACE([Description],',','')
      FROM ItemInformation
    
    SELECT REPLACE([Description],'C','')
      FROM ItemInformation
    
    SELECT CHARINDEX(',',[Description])
      FROM ItemInformation
    GO
    
    DECLARE @x varchar(80)
    SELECT @x = '‚COOKIES‚'
    SELECT @x
    SELECT REPLACE(@x,',','')
    GO
    
    DELETE FROM ItemInformation
    GO
    
    INSERT INTO ItemInformation([Description])
    SELECT 'CHOCOLATE, CHIP‚' UNION ALL
    SELECT 'CHOCOLATE, CHIP‚' UNION ALL
    SELECT ',CHOCOLATE, CHIP‚' UNION ALL
    SELECT ',CHOCOLATE, CHIP‚ ' UNION ALL
    SELECT ',CHOCOLATE, CHIP‚ A' UNION ALL
    SELECT ',CHOCOLATE, CHIP‚ , '
    GO
    
    SELECT REPLACE([Description],',','')
      FROM ItemInformation
    GO
    
    DROP TABLE ItemInformation
    GO

    BIZZARO WORLD
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    for the benefit of those of us who want to stay current, but who, for one reason or another, cannot actually run your script, would you kindly enlighten us with a brief synopsis or summary of your findings

    thank you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My Bad....

    It doesn't replace the commas....

    and CHARINDEX returns 0
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Using sql2000 sp3 with an extra security patch (8.00.818), I am not getting the commas to go away in all cases. That is pretty weird. Now I gotta see Microsoft's explanation of this one.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about the first update....NONE of the commas get touched....

    And how about the fact that it doesn't work on the local variable either...

    It's gotta be a bug with REPLACE...

    I'm gonna give a local table variable a shot....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope...local table variable doesn't work...nor dynamic SQL

    Anyone have a work around?

    Code:
    DECLARE @x TABLE([description] varchar(80))
    
    INSERT INTO @x ([Description])
    SELECT 'CHOCOLATE CHIP‚' UNION ALL
    SELECT '‚COOKIES‚' UNION ALL
    SELECT '‚CROISSANTS *PLAIN*‚' UNION ALL
    SELECT '‚DONUTS‚' UNION ALL
    SELECT '‚DONUTS *DOZEN*‚' UNION ALL
    SELECT '‚MUFFINS‚' UNION ALL
    SELECT '‚BAGELS‚' UNION ALL
    SELECT '‚ROLLS‚' UNION ALL
    SELECT '‚CUPCAKES‚' UNION ALL
    SELECT '‚CRISPIES‚' UNION ALL
    SELECT '‚DANISH/SWEET ROLLS‚' UNION ALL
    SELECT '‚FUDGE BROWNIES‚' UNION ALL
    SELECT '‚PUFF PASTRIES/ECCLES‚' UNION ALL
    SELECT '‚STICKY BUNS‚' UNION ALL
    SELECT '‚TURNOVERS‚' UNION ALL
    SELECT '‚BLACK & WHITE COOKIES‚' UNION ALL
    SELECT '‚LINZER TARTS‚' UNION ALL
    SELECT '‚SCONES/BISCUITS‚' UNION ALL
    SELECT '‚SCUFFINS‚' UNION ALL
    SELECT '‚SINFULL BITS‚'
    
    SELECT REPLACE([Description],',','')
      FROM @x
    
    GO
    
    DECLARE @sql varchar(8000)
    SELECT @sql = 'SELECT REPLACE([Description],'+ '''' + ',' + '''' + ','''') FROM ItemInformation'
    SELECT @sql
    EXEC(@sql)
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Brett: Try on this query. Left and substring correctly identify the first character, but the ascii() function seems to have a bit of ambiguity to it. Unfortunately, I do not have an ascii or unicode table at hand, so I am not sure what character 130 is. Or 44, for that matter. I can not imagine both would be commas.


    select substring (description, 1, 1), left (description, 1), ascii (substring (description, 1, 1)), ascii (','), unicode (substring (description, 1, 1)), unicode (','), char(130), char(44)
    from ItemInformation

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    SELECT REPLACE([Description],char(130) ,'')
    FROM ItemInformation


    And yes, this is too machine/regional setting/font/something specific to fly in my shop. But it gets you there. But still. Why 2 commas?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was going there nect NICE ONE...

    It originated from

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31914

    And Nigel has some great insight there....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    --Not so mysterious...
    select Replace('‚COOKIES‚', ',', '') --Fails
    select Replace(',COOKIES,', ',', '') --Works

    --Whats the diff?
    select ASCII('‚COOKIES‚')
    select ASCII(',COOKIES,')

    --The original string contains a character that looks like a comma, but is not.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well what is it?


    I did cut and paste from the guy's example....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Brett:

    Found an anwer to your zen question:

    http://www.kostis.net/charsets/cp1252.htm

    When it is a "SINGLE LOW-9 QUOTATION MARK"

  13. #13
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    Originally posted by blindman
    --Not so mysterious...
    select Replace('‚COOKIES‚', ',', '') --Fails
    select Replace(',COOKIES,', ',', '') --Works

    --Whats the diff?
    select ASCII('‚COOKIES‚')
    select ASCII(',COOKIES,')

    --The original string contains a character that looks like a comma, but is not.
    I searched (ascii 130) and it's the character (é) !! how would it look like (,) ?

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think I have it now. This being the Western European character set, we are looking at (I think) a spanish end quote mark. That's why I don't have this thing on my keyboard. Anyone who knows spanish a little better than my one year of high school, please feel free to correct me.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's a "base aligned quote" according to http://www.adobe.com/print/tips/feli...pdfs/codes.pdf

    it's a "low left rising single quote" according to http://www.bbsinc.com/iso8859.html and it even has an html entity! is it any surprise that this comes from the Microsoft Windows Latin-1 Added Characters set? in that regard, it is as bad as 149, the bullet which people always want to use in html documents and then wonder why it isn't visible in non-windows systems...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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