#1
  1. An Ominous Coward
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2002
    Posts
    4,425
    Rep Power
    0

    Access and Space Padding, or How I Wish It Wouldn't


    Access keeps space padding EVERYTHING in my tables. This makes searching the database near impossible. Say I have a text field that's 100 characters long. If I enter a 5 character word, the search to find it has to be that 5 character word and 95 spaces. How the hell do I get around that without adding a % to the end of every single thing I search for? I would like to reserve the % as a wildcard in my search that USERS can enter manually, not something I have to hardcode into the system to get around the stupid database. Is there a way to turn of space padding in Access fields (the typically worthless help file either doesn't know or aint tellin')? Since Jet SQL syntax is about the most endangered species on earth (like most other things that are Microsoft documentation), I have no idea if there's a good SQL solution to this. Typical of the "support" site (which I think has answers about 5% of the time I look at it), there's absolutely no mention of this....

    Curiosity... do ALL databases do this, or just the more.. um.. neurotic ones? I don't recall having this problem with PostgreSql... but I haven't used it in so long I can't remember.

    --------------------
    ETA to RH 8.0 Download Completion: ~ 7 days, 12 hours.
    --------------------
  2. #2
  3. An Ominous Coward
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2002
    Posts
    4,425
    Rep Power
    0
    I found this in the pgsql faq (or.. what there is of it):
    ** CHAR vs. VARCHAR vs. TEXT

    CHAR is space-padded, so the field actually contains 'foo '. However, searching for 'foo' will match, as will 'foo ' and 'foo '. This works directly in pgsql, as well as in Access (either with Jet queries or pass-through queries.)
    This is obviously a misunderstanding on my part. I was thinking that LIKE 'foo' (no wildcards) was similar to saying = 'foo' when in fact it's a totally different behavior. So, my new question is:

    How can I use a LIKE query in my code so that the user can enter a % wherever they want. In other words, I want this:
    Code:
    SELECT something FROM table WHERE column LIKE '$foo';
    to act like this:
    Code:
    SELECT something FROM table WHERE column = '$foo';
    if the user didn't enter any wildcards. I'd prefer not to have to set up two different code chunks to accomplish it, if possible...
  4. #3
  5. An Ominous Coward
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2002
    Posts
    4,425
    Rep Power
    0
    I am now officially confused. I found a Jet SQL reference that lists this example:
    Code:
    SELECT AuthorName FROM BookList
    WHERE BookType LIKE 'Cook Book'
    But that will fail for me if 'Cook Book' is in a 50 character text field (actually, any text or memo field longer than 9 characters.... To make it work, I have to say:
    Code:
    SELECT AuthorName FROM BookList
    WHERE BookType LIKE 'Cook Book%'

IMN logo majestic logo threadwatch logo seochat tools logo