Hi Puneet, I probably shouldn't have said that they 'ignore' the blanks, but they are capable of treating them as white space for text matching purposes. I can't speak for Oracle, but I'm pretty sure MySQL and SqlServer (and I know for sure DB2) all allow you to search on 'A' and they will return records for 'A ' or 'A ', etc. I think it is intuitive to treat trailing blanks as whitespace, but that might be because of my main experience with DB2/400.
One difference, of course, is that these other databases allow you to define character fields with lengths, as opposed to just TEXT. I suppose that inherently means that TEXT is literal (meaning that it recognizes the blank as its ASCII character), where a CHAR(35) field would know, within the context of its defined length, how many trailing blanks it could ignore. I thought about the like idea, but 'AA' is potentially valid as well, so ultimately that will create its own problems. And in this particular case, this one is a key fields, so EQUAL matching is pretty necessary. I do think more and more that the solution for me is to trim the trailing blanks before INSERTing them into SQLite. Thanks for your input, -- Joel Cochran Stonewall Technologies, Inc. On 3/26/07, P Kishor <[EMAIL PROTECTED]> wrote:
On 3/26/07, Joel Cochran <[EMAIL PROTECTED]> wrote: > Howdy all, > > I am new to SQLite, so I hope this isn't too much of a newbie question, but > I searched the Internet, the archives, and the help docs and could not find > any mention of this. > > I am populating an SQLite database from a legacy IBM AS/400 database. The > 400 stores all character fields with padded blanks. As a result, when I > export the data the blanks remain. Normally this is not a problem, but I > noticed in SQLite when I do a select statement the MYFIELD = 'A' will not > work if the data is in fact 'A ' (a trailing blank). Try MYFIELD LIKE 'A%' > > SQLite apparently does not ignore trailing blanks on character matching like > other DBs do. I am not sure they do. I am speaking from memory, but I am pretty sure that both SQL Server and Oracle don't just ignore blanks. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ --------------------------------------------------------------------- collaborate, communicate, compete ===================================================================== ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
