I have some memo fields which have been filled with data, but too many carriage returns.

This is the result of concatenating a series of VARCHAR fields without thinking things
through properly, the empty fields were added as additional returns, not by me, I am not
sure exactly how.

Now we have many records with poorly formatted data.

The issue is that I do not want to remove ALL the carriage returns, only when they are
duplicated.

i.e. the data should be in the form

1 High St
Some District
SomeTown

NOT

1 High St

Some District


SomeTown

OR

1 High StSome DistrictSomeTown

There are several 10k of data.

--

I want to run SQL in the form:

UPDATE Addresses
SET
StreetDetails = REPLACE(#13#13 WITH #13 IN StreetDetails);

... but this does not work, nor does:

UPDATE Addresses
SET
StreetDetails = REPLACE('#13#13' WITH '#13' IN StreetDetails)

--

- How should I express this?
- How can I easily see the CHAR contents of the memo field, to assess what non-visible
characters it contains?

Thanks Adam

adam


You need a plus between each #character. I'd also suspect that its crlf rather than just cr so

update Addresses set StreetDetails= replace(#13+#10+#13+#10,#13+#10,StreetDetails)


Roy Lambert[Team Elevate]

Thank you Roy, I would have torn my hair out before I would have figured that out ...
though probably it is in the help files somewhere!

Sorry Roy, your help has't got me anywhere ... I am starting to suspect there may be other
non-visible characters present in the field.

New question:

Any way I can see the CHAR contents of a MEMO field, to examine exactly what non-printing
chars are present?

adam


If the memos aren't to big what I used to do was open in Delphi and loop through dumping the intostr(counter) & inttostr(Ord(memofield.asstring[counter])) to a memo component.

As an added refinement you can just do that if memofield.asstring[counter]< #32.


Roy Lambert [Team Elevate]

Adam try copying the memo then
paste it into notepad. Look to see
any characters there.
If not open Wordpad paste it there
and check.
I download a csv file daily and it contains
a little square I guess a carrige return if
I view it in notepad. So I save it as a txt
then open  it in Wordpad the characters
go and I have a nice text delimited file.
Not what you want but maybe you can see
the same little square.
As another bonus I have the racing SP's
in one long string upto 30 sometimes
75 lines such as below.
5/4 Alpha, 6/4 Bravo, 2/1 Charlie, 3/1 Delta     ++++++++
I need to get them into a text delimited file
I just go to wordpad after copying the little
square do a replace and the above all go to

5/4 Alpha
6/4 Bravo
2/1 Charlie
3/1 Delta

Hundreds then I get a comma between in another way
to seperate into 2 fields.
HTH


"adam" <adam@fullwellmill.co.uk> wrote in message
news:F7E413DC-46E2-4C8D-8C35-4C558C68997F@news.elevatesoft.com...
> Sorry Roy, your help has't got me anywhere ... I am starting to suspect
> there may be other
> non-visible characters present in the field.
>
> New question:
>
> Any way I can see the CHAR contents of a MEMO field, to examine exactly
> what non-printing
> chars are present?
>

Forgot to attach file to see if it
looks same on everyone elses computer
the little square is visible in notepad but
goes in Wordpad
Rita

"Rita" <nospam@nospam> wrote in message
news:ADFAC29C-2C04-4D61-AE7C-60728575FF87@news.elevatesoft.com...
> Adam try copying the memo then
> paste it into notepad. Look to see
> any characters there.
> If not open Wordpad paste it there
> and check.
> I download a csv file daily and it contains
> a little square I guess a carrige return if
> I view it in notepad. So I save it as a txt
> then open  it in Wordpad the characters
> go and I have a nice text delimited file.
> Not what you want but maybe you can see
> the same little square.
> As another bonus I have the racing SP's
> in one long string upto 30 sometimes
> 75 lines such as below.
> 5/4 Alpha, 6/4 Bravo, 2/1 Charlie, 3/1 Delta     ++++++++
> I need to get them into a text delimited file
> I just go to wordpad after copying the little
> square do a replace and the above all go to
>
> 5/4 Alpha
> 6/4 Bravo
> 2/1 Charlie
> 3/1 Delta
>
> Hundreds then I get a comma between in another way
> to seperate into 2 fields.
> HTH
>
>
> "adam" <adam@fullwellmill.co.uk> wrote in message
> news:F7E413DC-46E2-4C8D-8C35-4C558C68997F@news.elevatesoft.com...
>> Sorry Roy, your help has't got me anywhere ... I am starting to suspect
>> there may be other
>> non-visible characters present in the field.
>>
>> New question:
>>
>> Any way I can see the CHAR contents of a MEMO field, to examine exactly
>> what non-printing
>> chars are present?
>>
>
>

"Rita" <nospam@nospam> wrote in message
news:ADFAC29C-2C04-4D61-AE7C-60728575FF87@news.elevatesoft.com...
> Adam try copying the memo then
> paste it into notepad. Look to see
> any characters there.

There are plenty of free programs to examine the characters in a file.

google view characters in file

Robert