Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Unanswered: Extra characters in VARCHAR fields

    I am creating an ADODB recordset with a SQL statement from VB against a MySQL db. The values in any VARCHAR fields are padded with null characters.

    For example, if I have a field 'Name' defined as VARCHAR(32) with a row containing "Smith", the value returned to the VB application is "Smith***************************" where * is a null character (27 of them in this case).

    I know this may be idiocy on my part, but as a bit of a newbie, any ideas, suggestions, or solutions are greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    I doubt that it's padded with NULLS. Try this at the MySQL command prompt:

    Code:
    SELECT name, ASCII(LEFT(name,1)),
     ASCII(MID(name,2)),
     ASCII(MID(name,3)),
     ASCII(MID(name,4)),
     ASCII(MID(name,5)),
     ASCII(MID(name,6)),
     ASCII(MID(name,7)),
     ASCII(MID(name,8)),
     ASCII(MID(name,9)),
     ASCII(MID(name,10))
    FROM party 
    WHERE name = 'Smith'\G
    ...and see what you get.
    Bradley

  3. #3
    Join Date
    Apr 2003
    Posts
    3
    Ok, I tried it.

    The characters past the 'h' in 'Smith' have an ASCII value of '0' (zero). Please tell me this is what you expected.

    Does this somehow explain why VB is padding the field?

    (I removed the '\G' from the end of the query you posted. I hope there was not some purpose for it. Please advise.)

    Thanks much for your help so far, but I'd like to hear back from you with further insight.

  4. #4
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Oh - I thought that you meant that the database was sending the data padded to VB.

    Are you sure VB is padding with NULLS? Try some code to show you the ASCII value of each character. It's more than likely just spaces, which you can get rid of with VB's TRIM function.
    Bradley

  5. #5
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Extra characters in VARCHAR fields

    Originally posted by goob
    I am creating an ADODB recordset with a SQL statement from VB against a MySQL db. The values in any VARCHAR fields are padded with null characters.

    For example, if I have a field 'Name' defined as VARCHAR(32) with a row containing "Smith", the value returned to the VB application is "Smith***************************" where * is a null character (27 of them in this case).

    I know this may be idiocy on my part, but as a bit of a newbie, any ideas, suggestions, or solutions are greatly appreciated.

    Thanks.
    These are NULL characters. Yep, MySQL pads the returned rows with them. Had the same behavior with MySQL and ODBC. Seems like you have to trim the results...

  6. #6
    Join Date
    Sep 2003
    Posts
    4
    Not sure how to solve your problem with padding going IN. But you can trim the junk on the way OUT.

    SELECT TRIM(TRAILING '0' FROM `Smith00000000000`);
    -> 'Smith'

    Should help if I understand your problem. Found this while looking for a solution to my own problem (still looking).

    How do I specify a wildcard to do a similar thing as above?

    Tried:
    SELECT TRIM(TRAILING ' (%' FROM `Smith (last name)`);
    Doesn't work but basically I want it to return:
    -> 'Smith'

    Thanks.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT TRIM(TRAILING ' (%' FROM `Smith (last name)`);
    Doesn't work but basically I want it to return:
    -> 'Smith'
    Code:
    select case when locate(' (',astring)>0
                then left(astring,locate(' (',astring)-1)
                else astring
            end as trimmed
    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
  •