On 19.12.2008, at 17:48, John Toliver wrote:
JT - I wasn't sure what other information to provide. But I'm running
Openoffice 3, Base. Using the standard HSQLDB for the backend. Using
GNU/Linux 2.6.24-21-generic.
So you might want to check online-documentation for HSQLDB (google it
up, you might even try HSQLDB TRIM RTRIM)
So what I would try is create a view or a query that filters the
unwanted
characters out of your raw data (sort of a parser). Rather than
reading the
table directly you would read the filtered data from the query.
JT - Can I write a query that is told essentially to look for, and
ignore
"nothing" or "null"? I am imagining that the query would look at
the whole
field instead of at the last part of an entry.
Well, depends on what you mean by "nothing" or "null". If it's spaces,
linefeed, tabs ('whitespace'), then it should be possible, it is
actually a very common requirement, depending on HSQLDB's builtin
functions, removing several types of trailing spaces might be a bit
tricky though. Formally one shouldn't consider trailing "cruft"
nothing, and certainly not "null", the later being a very special,
somewhat difficult to understand term in database lingo.
I see there is is TRIM, LTRIM (left-trim), RTRIM (right-trim) and
TRIM(leading/trailing/both ... FROM ...) in HSQLDB, but I can't tell
at first sight, whether it removes all types of whitespace or only
one. There could be more functions of similar type and with richer
functionality, scan the documentation for them. When I tried it, RTRIM
displayed a surprising behaviour: it would strip all trailing
characters of whichever type was the last in my data, so if space,
then all trailing spaces, if linefeed, then all trailing linefeeds -
but not both! Using RTRIM recursively seems not to be possible
RTRIM(RTRIM(memofield)), but try on your system (I'm on Mac).
The basic SQL-query term would always be a variation of:
SELECT SOMETRIMFUNCTION(memofield) FROM YourTable;
Sorry for not being more specific, I have very little experience with
HSQLDB, but I am quite sure that studying the HSQLDB-documentation
(rather than OpenOffice-Base) at some leisure should give you exactly
what you need. Your problem is very common. You might also google for
"HSQLDB removing trailing spaces"...
Best
Rainer
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
