#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1

    Hex Conversion wrong result


    What would be the best way to convert some values to hex in mysql ?
    I have tried to convert values from char(20) into hex values for varchar(40) but it always gives me wrong results.


    For example i have this value in char(20) *ިtSt but when i convert it to hex in varchar(40) i get C38CC3912AC29DC2B6CB9CC2BBC2B4E280B9C281 which is wrong. The correct value should be CCD12A9DB698BBB48B81DEA8745304031B9574DF. The hex string is totally broken...
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    Originally Posted by pwpx2
    *˜‹ިtS•t
    how are you doing the conversion?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1
    UPDATE tablename SET col2name =hex(col1name); col2name is the col in varchar(40) and col1name is the char(20) one
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1
    ALTER TABLE tablename ADD `col2name` varchar(40) COLLATE utf8_unicode_ci NOT NULL; this is how i add the col2. Does it matter if col1 is utf8_general_ci ?
  8. #5
  9. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,333
    Rep Power
    9645
    Actually the conversion is correct.

    Characters and bytes are two different but related concepts. You see the character but that character has to be mapped into bytes for it to be stored. col1name is set to use the UTF-8 encoding, and that will represent the character as 0xC3 0x8C. If you want the byte 0xCC then you need to be using the Latin1 encoding instead; converting the column in-place will keep the same logical characters but change the underlying bytes, or you can leave it as-is and convert in the query with
    Code:
    HEX(CONVERT(col1name USING latin1))
    If you're storing binary data in this column then you need to be using a binary-safe column type, ie. BLOB(20). That will not use any encoding at all and will store whatever bytes you tell it to. Your application may need to be updated to support this change, as MySQL will no longer do any character encoding conversions for you, however this would likely only impact retrieving and displaying the data (and not storing it).

    Comments on this post

    • pwpx2 agrees : great help.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1
    so i should do UPDATE tablename HEX(CONVERT(col1name USING latin1)); ?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1
    Tried it, doesn't work. Definetly doing something wrong A step by step command list would be so useful.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1
    I have used this:
    Code:
    SELECT HEX(CONVERT(`colname` USING latin1)) AS latin1 FROM tablename
    and indeed it does the conversion but it didnt changed anything in the col. It just printed the values
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    Originally Posted by pwpx2
    but it didnt changed anything
    try
    Code:
    UPDATE tablename
       SET col2name = HEX(CONVERT(`col1name` USING latin1))

    Comments on this post

    • pwpx2 agrees : great help.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1
    One second..Yes ti works. thank you so much. this is a life savior. I've been struggling for the past 2 days.. I learned something new. Thanks.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1
    Works! Thank you. rep added
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,712
    Rep Power
    4288
    thanks, but rep should go to @requinix who diagnosed the collation problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    55
    Rep Power
    1
    Also added to him

IMN logo majestic logo threadwatch logo seochat tools logo