Drew Jensen wrote:
Just wanted to let you know that I can't do the script this morning -
will find time this afternoon for a script and a better explanation of
what follows.
But a quick reply for now
Meantime - if you just need to remove Carriage Returns from the end of
the data this will do it.
1st - BACKUP YOUR DATABASE FILE
Second - if one way fails, just change the data...so I need to use a
character not found in your data to act as a place holder for the CR's.
In my example I use a tilda "~" - if you don't know how to check to
see if one of these is already in your data something like this will
do it
SELECT COUNT(*) FROM "Table1" WHERE LOCATE( '~', "ColName") > 0
If the return value of that query is 0 then there are no tildas in any
record in that column, if it returns 1 or more then there are.
Copy these lines to a text editor and change the table and column
names appropriately.
If you are using Windows most likely you need to use CHAR(13), if
Linux then CHAR(10) BTW.
UPDATE "TableName" SET "ColName" = REPLACE( "ColName", CHAR(13), '~' );
UPDATE "TableName" SET "ColName" = TRIM( TRAILING '~' FROM "ColName" );
UPDATE "TableName" SET "ColName" = REPLACE( "ColName", '~', CHAR(13) );
NOTE that each line ends with a semi-colon.
Then copy the changed lines to your clip board.
Open the SQL window, paste them in and execute.
That should do it...for Carriage Returns anyway..
Till Later,
Drew
Drew, I looked for TRIM in hsqldb.org/doc/guide/ch09.html and couldn't
find it in the Built in Functions and Stored Procedures list -- LTRIM
and RTRIM were there, though. Any idea why? I know I found it once upon
a time!
Earlier in the thread, John indicated that what he wanted was to find
the last period (full stop) and eliminate everything after it. Too bad
there doesn't seem to be a version of LOCATE to find the last instance
of something rather than the first! A search direction parameter for it,
or a LOCATELAST function, would be really handy, I'd think.
Can the REPLACE function take a concatenated string like
CHAR(13)+CHAR(10), for example? Trimming the individual characters
wouldn't be enough if they're interspersed with other "whitespace"
characters.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
