Marc I have been staring at this code you wrote for the past two days and
I'm not able to make sense of it. I need a little more help. Now I
understand that the two lines are dependent on how the data in my fields is
structured. It's simple text, like any normally formatted paragraph. Is
their something I can do to the DB to analyze it to figure out? With that
question asked see below:
> If Johns data does not have sentences or parts separated by NL or CR
> chars he could use sth. like:
>
> LEFT(TheString, POSITION ('.'+CHAR(10) IN TheString)+1)
JT - I don't think the above line would work then because I have many with
paragraphs separated by either a line feed (is that char10?) or a carriage
return (is this char13). I'm not sure how to tell which is which as I
haven't manually written all the data in each field. Some of it has been
pasted from the web or other sources. Hence my question above about
analyzing the data.
> If there are concatenated sentences this would only work for double
> newlines, leaving on extra NL:
>
> LEFT(TheString, POSITION ('.'+CHAR(10)+CHAR(10) IN TheString)+1)
I don't think I have concatenated sentences. The text would read as normal
information.
> > 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.
>
JT - I went to the site suggested by Barbara, (
http://hsqldb.org/doc/guide/ch09.html) and found this:
"REPLACE(s,replace,s2)
replaces all occurrences of replace in s with s2" Which I think
means if I were to open an SQL window for this, it would replace all
instances of the phrase "replace" in the table named "s" with the new phrase
"s2"? If so then can I use this on an entire table? I'm not clear on the
implementation of the command, incidentally the same question I have about
the code you originally mentioned. Would I place the variation of this text
in the original RTRIM string we looked at?
