JT- I changed the single quotes to double quotes and the response was
"command completed successfully. I set up my test this way: I went into one
of the records in the column "Major/Common Side Effects" and I hit "Enter" a
dozen or more times which should be the Carriage Return correct? I then
saved the record so that it now looked like the text at the bottom of the
email ( I placed it here so the conversation wouldn't be overly messy (btw
would it be better to use pastebin for this type of thing?).
When I went back to check the record after running the command, it still had
all the CR's at the end even though HSQLDB says "command completed
successfully". It Didn't do what I expected it to do. I went back to the
original reference page but I don't see another command that matches what
I'm trying to do.
Hi JT,
ah well, there is a little bit of a disconnect going on here.
The RTRIM command removes SPACE characters. Ascii value 32 characters
and that is the only thing it removes.
Sound like you are talking about wanting to remove all "White space" -
commonly used to mean SPACE, CR, CR/LF, and TAB characters.
Yes?
OK - so looking at the definition for the TRIM command on the wiki page:
"Returns the character sequence s, with the leading, trailing or both
the leading and trailing occurences of the first character of the
character sequence trimstr removed.
If trimstr is not supplied SPACE is used.
TRIM( BOTH FROM ' ONE ' ) = "ONE" ; TRIM ( BOTH 'O' FROM 'OONEOO' ) = NE
* Note with escape processing DISABLED the command may be abbreviated to
TRIM ( FROM ' ONE ' ) AS "OUTPUT" = "ONE" - An alias must be used in
this case also "
You will notice that this command lets you set WHAT to remove.
So conceivably this would mean that a series of commands such as:
UPDATE "TabeName" SET "ColName" = TRIM(TRAILING CHAR(7) FROM "ColName");
UPDATE "TabeName" SET "ColName" = TRIM(TRAILING CHAR(13) FROM
"ColName"); -- Win
UPDATE "TabeName" SET "ColName" = TRIM(TRAILING CHAR(10) FROM
"ColName"); -- Unix
UPDATE "TabeName" SET "ColName" = TRIM(TRAILING FROM "ColName");
OR
UPDATE "TabeName" SET "ColName" = TRIM(TRAILING '/t' FROM "ColName");
UPDATE "TabeName" SET "ColName" = TRIM(TRAILING '/n' FROM "ColName");
UPDATE "TabeName" SET "ColName" = TRIM(TRAILING FROM "ColName");
would do it.
However the current HSQL parser (trying this with OO.o 3.0.1 RC1 under
XP) seems to break on all these...arrgh.
I would say it is a bug because as soon as the escape character is
introduced it breaks, but non white space characters work just fine - is
that irony at play here??
So for today looks like you would need to fall back onto a small basic
script to do this.... which will have to wait till morning I think.
Till then,
Drew
--
OpenOffice.org User Community Forums
Connecting users around the globe!
Community forum http://user.services.openoffice.org/en
Communauté francophone http://user.services.openoffice.org/fr
Közösségi fórum http://user.services.openoffice.org/hu
コミュニティーフォーラム http://user.services.openoffice.org/ja
United States PostgreSQL Association: http://www.postgresql.us/
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
