andrew wrote:
Hmm. In the Execute SQL Statement dialog, I had
UPDATE "Addresses" SET ADDR = TRIM ( ADDR )
Hi Barbara,
I believe it was Mr Reagan that said - 'Trust but verify."
Out of the box HSQL has
LTRIM = Left Trim
RTRIM = Right Trim
But no TRIM... if you could see how hard I am laughing right now
Anyway -UPDATE "Addresses" SET ADDR = LTRIM ( RTRIM( ADDR ) ) - does
do a full TRIM.
Again, the commands are in the Users Guide at http://hsqldb.org or a
quick list can found in this document.
http://www.paintedfrogceramics.com/OpenOffice/hsql_functions.ods
Sorry for the mistake.
Drew
When I tried this as a query, that didn't work either:
SQL Status: 00000
Error code: -155
Statement does not generate a result set
I did find TRIM itself, too, just not where the others are:
TRIM( LEDING ... FROM ...)^[1
<http://www.hsqldb.org/doc/guide/ch09.html#ftn.posthyper>]
|TRIM([{LEADING | TRAILING | BOTH}] FROM <string expression>)|
So I tried again with
UPDATE "Addresses" SET "ADDR" = TRIM(BOTH FROM "ADDR" )
SQL Status: 00000
Error code: -155
Statement does not generate a result set
So both ways were pretty convinced that they aren't finding anything,
though the table Addresses exists, and the field ADDR in it contains
data like " AcoCT000 " (without the quotes, of course). I tried it
without the quotes around ADDR, too, just in case that made it treat it
as a literal string, but got the same result. Running direct didn't
accomplish anything, either.
Then I went back to the Execute SQL Statement dialog -- and HOORAY! This
worked like a charm, with either the composite LTRIM/RTRIM or the TRIM.
The help text should probably be updated to remove that warning about
admin commands only.
So I now have my database where I want it, at least for now! And I know
how to find the other neat stuff, which I'm sure will come in handy. I
also developed some forms, because I thought they might let me do wraps
and normal editing functions like Find/Replace, but no luck there - even
though it's using Writer, and I can choose a layout that looks a whole
lot like a table, it doesn't behave like one. So I'll just keep working
from the queries based on the Violations table only, at least until I go
to 2.3.
Thanks a million for all your help!
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
