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

Dev Shed Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

October 10th, 2002, 12:32 PM

Ctb

An Ominous Coward

Join Date: Jan 2002

Posts: 4,425

Time spent in forums: 3 Weeks 10 h
Reputation 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.
--------------------

October 10th, 2002, 01:38 PM

Ctb

An Ominous Coward

Join Date: Jan 2002

Posts: 4,425

Time spent in forums: 3 Weeks 10 h
Reputation Power: 0

I found this in the pgsql faq (or.. what there is of it):

Quote:

** 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...

October 10th, 2002, 01:51 PM

Ctb

An Ominous Coward

Join Date: Jan 2002

Posts: 4,425

Time spent in forums: 3 Weeks 10 h
Reputation 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%'

Viewing: Dev Shed Forums > Databases > Database Management > Access and Space Padding, or How I Wish It Wouldn't

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump