Replace Space + Return with Return

I've got a Table called ALLDATA
It's got a memo field called DATA1

DATA1 has hundreds of extraneous spaces followed by a return.

Can someone give me the update query code/SQL to eliminate the extraneous spaces.

Thanks
LVL 24
EirmanChief Operations ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
quick and dirty, but not accurate, since the number of spaces is not known
run this query


Update AllData
set data1=replace(replace(replace(replace([data1], space(5),space(1)), space(4),space(1)), space(3),space(1)), space(2),space(1))


you may have to run it more than once..
0
EirmanChief Operations ManagerAuthor Commented:
Thanks capricorn1  The wording of my question was poor and confusing.
(I haven't tried your code yet)

I don't have hundreds of spaces in a row ... I have hundreds of ...

Text
Text
single space + return
Text
single space + return
Text
Text
return
Text
single space + return
single space + return
Text
Text
single space + return
Text
Return
Text
Text
single space + return
Text

And I want to eliminate all the single spaces where it is followed by a return
0
GaryCommented:
This may work depending on the kind of CR

UPDATE ALLDATA set DATA1= replace(DATA1,' \n','');

Always backup your DB before performing these kinds of mass updates.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Rey Obrero (Capricorn1)Commented:
ok try this

Update AllData
set data1= replace([data1], chr(32) & chr(13) & chr(10),chr(13) & chr(10))

if this doesn't work, upload a sample db with the table.. AllData
0
EirmanChief Operations ManagerAuthor Commented:
My table is called VOCAB and the first field I'm working on is called DEFINITION

Nothing changes when I run this (I do get an error)

    DoCmd.RunSQL "Update VOCAB Set DEFINITION = Replace([DEFINITION], Chr(32) & Chr(13) & Chr(10), Chr(13) & Chr(10))"

Is the above correct? I'll post a DB if necessary.

======================================================= EDIT ======
This is strange .... just before I clicked SUBMIT on this post I manually deleted some of the SPACES .... but they keep coming back after I save .... It seem to be a 'feature' of memo fields.

I manually deleted a few hundred spaces yesterday, but they are all back now.


They are only a minor nuisance and I can live with them.
0
Rey Obrero (Capricorn1)Commented:
yes, that is correct..

yes, better upload a sample db with the table.

what version of Access are you using?

 If A2007 or above, check the memo field property "Append Only" setting.
If it is YES then, you can not edit the content.
0
EirmanChief Operations ManagerAuthor Commented:
I'm using access 2010 and Append only is NO on all memo fields.

DELETE SPACES on the switchboard form runs your code.

Here is my vocab database (a work in progress) of familiar words (or that ring a bell) but don't I use in conversation or writing. There no personal info so it's here in it's entirety.

Go to the second word on VOCAB A-D (ABROGATE)

Repeal or do away with (a law, right, or formal agreement)
SPACE + RETURN
To treat as non-existent (abrogating their responsibilities)

How do I get rid of the pesky SPACE

Most of the content is pasted from the web and edited.
GENERAL.accdb
GENERAL-TABLES.accdb
0
Rey Obrero (Capricorn1)Commented:
Looks like your information came from a web page, is this correct?

here is the content of record 89 (abrogate)


<div>Repeal or do away with (a law, right, or formal agreement)</div>

<div>&nbsp;</div>

<div>To treat as non-existent (abrogating their responsibilities)</div>


.
0
EirmanChief Operations ManagerAuthor Commented:
As I said .....
Most of the content is pasted from the web and edited.
0
Rey Obrero (Capricorn1)Commented:
how do you want this content to look like


<div>Repeal or do away with (a law, right, or formal agreement)</div>

<div>&nbsp;</div>

<div>To treat as non-existent (abrogating their responsibilities)</div>
0
EirmanChief Operations ManagerAuthor Commented:
I think this is what I want ... A simple return with no preceding space.


<div>Repeal or do away with (a law, right, or formal agreement)</div>
<div>
<div>To treat as non-existent (abrogating their responsibilities)</div>
0
Rey Obrero (Capricorn1)Commented:
don't you want it to be just like this


Repeal or do away with (a law, right, or formal agreement)
 
To treat as non-existent (abrogating their responsibilities)
0
EirmanChief Operations ManagerAuthor Commented:
That's the way it looks at present .... the only thing is there is a nuisance space between the two lines. Try deleting the space within the database ..... it's gone .... click on another record and go back ..... the space is back!

The invisible space is no problem until I want to enter another line of text .... I have to backspace before typing.

Thanks for your persistence in this. The only way to see the problem is within the database itself

EDIT ...

Repeal or do away with (a law, right, or formal agreement)
 
To treat as non-existent (abrogating their responsibilities)


The above has a space between the lines (Highlight it) .... I want to eliminate the space.
ORANGE Point To The Surplus Space
0
Rey Obrero (Capricorn1)Commented:
check the table VOCAB from this back end..

query1- removes all the html tags
it call the function HtmlToText

query2 - removes the extra space and CR


sample revised text


Repeal or do away with (a law, right, or formal agreement)
To treat as non-existent (abrogating their responsibilities)
GENERAL-TABLES.accdb
0
EirmanChief Operations ManagerAuthor Commented:
Thanks capricorn1, I'll try it over the weekend and post back here on Monday.

Just reading your accompanying text
query one may do what I want, query two won't as it removes the CR
0
EirmanChief Operations ManagerAuthor Commented:
Belated Points!
Somehow, this question seemed to get lost in the system.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.