Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Special Character;strange

    I had to update pricelist at local-db on the basis of prices in another SQL by matching the description. When i JOIN the tables, no record gets displayed, but the descrptions at both tables were exactly the same i believed. Took a lot of time in identifying the discrepancy; the data was like this:

    CREATE TABLE #ABC (price money, DESCRIPTION VARCHAR(200))
    GO
    INSERT #ABC SELECT 19999,'SQL2000
    '
    GO
    SELECT * FROM #ABC WHERE DESCRIPTION ='SQL2000'
    --(0 row(s) affected)
    SELECT * FROM #ABC WHERE LTRIM(RTRIM(DESCRIPTION))='SQL2000'
    --(0 row(s) affected)
    select * from #ABC WHERE REPLACE(DESCRIPTION,'
    ','')='SQL2000'
    --(1 row(s) affected)
    GO
    DROP TABLE #ABC
    I believe, ENTER has some value within a field.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It's a combination of CHAR(13) + CHAR(10) which is LINE_FEED + CARRIAGE_RETURN, also known as 0D0A.You can modify your statement to:

    SELECT * FROM #ABC WHERE replace(DESCRIPTION,char(13)+char(10), '') ='SQL2000'

    or

    SELECT * FROM #ABC WHERE DESCRIPTION ='SQL2000' + char(13)+char(10)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    also known as 0D0A.

    That would be Y0D0A'a brother...

    "Do or Do not...there is no try"
    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
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Brett Kaiser
    That would be Y0D0A'a brother...
    Huh? What does that mean?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Huh? What does that mean?
    The Force rdjabarov, use The Force!

    -PatP

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Brett Kaiser
    That would be Y0D0A'a brother...

    "Do or Do not...there is no try"
    Evidently
    We
    Owe
    Kaiser ... for this one


  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ewwwww!

    -PatP

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Pat Phelan
    Ewwwww!

    -PatP


    No thanks necessary ...

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    But pardon me...
    Let's look at Kaiser's HEX representation of 0D0A
    Other than trying to be cute...
    What else is he trying to say?
    ...
    Mind you, Y cannot be found in HEX "alphabet"
    Either way, what's the point to all this?


    Enlighten me, please!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Other than to take a moment to decompress, share a bit of humor (or humour) and look at the little green alien (who absolutely rocks in Attack of the Clones against Lord Duku), not a darn thing!!

    ... A long, long time ago in a galaxy far, far away ...

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Is this thread slowly turning into a thematic AOL chat room? Where's this Uninstall option?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    But pardon me...

    Why, what happened? What did you do?
    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.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    Is this thread slowly turning into a thematic AOL chat room? Where's this Uninstall option?

    When did you get so cranky?

    Wait, wrong question.

    Did you miss lunch?
    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.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Brett Kaiser
    ...Did you miss lunch?
    I actually did, but the reason for my "layered" answer was inspired by tomh53's post
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Thank You, folks.

Posting Permissions

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