Using REPLACE in select statement to get rid of unwanted white space

PHP
1

I’m trying to get rid of double spaces or paragraph spaces from a column in the database that has the input from front end client display.

Whats happening is that the client wants to upload an excel file and their software doesnt like it when there is like a double space between words or the user has clicked enter to create a paragraph. Its fine that they do that their side, but before being exported to a csv file I wanted to basically clear any whitespace over a single space to a single space, and I thought I would do it the way below using REPLACE, but it doesnt like it.


$x="select Name, Employerofperson, Contactphone, Location, DATE_FORMAT(DateOccured, '%e-%c-%Y %H:%i') as DateOccurred, ProductionZone, ".REPLACE(NearMissDetails, "  ", " ").", ".mysql_real_escape_string(Anyimmediateactions).", HazardorNearMiss, Primarycause, Rottcause, Behaviours, PotentialOutcome, Likelihood, Comments, PersonsMostLikelyInjured, CloseDate, HighPotentialIncident, HPIInvestigatedBy, BUHSELeader, BUSectorLeader, HPIInvestigationConclusion, HPIBriefingNoteRef, HPIInvestigationCloseOutDate FROM hazzard WHERE ".$sqlstar." ORDER by DateOccured ASC";
2

Have tried another way now, and still no cigar.

I changed it to:


".preg_replace('/[[:blank:]]+/', ' ', NearMissDetails).",

But the paragraph space I created is still there -

The data in the database looks like this -

Gate left open on level 2 leading to haki stars by sub contractor

lee

When I would like it to close that big gap up and be like this -

Gate left open on level 2 leading to haki stars by sub contractor lee

3

Thought I’d give it a go outside the select statement.


$query = str_replace('  ', ' ', $query);

I’m guessing there is a different way to represent a double space instead of actually creating a double space, so changed it to this.


$query = preg_replace('/\\s{2,}/', ' ', $query);

Dont get any white out problems, but its not clearing up the space created by the user clicking enter or the space bar in one of the fields.

Do I need to specifically target that field.

This is what It looks like now.


$x="select Name, Employerofperson, Contactphone, Location, DATE_FORMAT(DateOccured, '%e-%c-%Y %H:%i') as DateOccurred, ProductionZone, NearMissDetails, ".mysql_real_escape_string(Anyimmediateactions).", HazardorNearMiss, Primarycause, Rottcause, Behaviours, PotentialOutcome, Likelihood, Comments, PersonsMostLikelyInjured, CloseDate, HighPotentialIncident, HPIInvestigatedBy, BUHSELeader, BUSectorLeader, HPIInvestigationConclusion, HPIBriefingNoteRef, HPIInvestigationCloseOutDate FROM hazzard WHERE ".$sqlstar." ORDER by DateOccured ASC";

$_SESSION['currentQuery5'] = $x;

$query = $_SESSION['currentQuery5'];

$query = preg_replace('/\\s{2,}/', ' ', $query);
4

Have gone back to the select statement and tried:


TRIM(both '\\r\
' from NearMissDetails ) as NearMissDetails,

And didnt work, from reading others posts it might be a job for jscript, so will keep on looking into it.

5

Unfortunately LOL i give up on this now.

I tried below and that also didnt work either, so either its not recognising the break in between the text or ive got it wrong.


$x="select Name, Employerofperson, Contactphone, Location, DATE_FORMAT(DateOccured, '%e-%c-%Y %H:%i') as DateOccurred, ProductionZone, NearMissDetails, ".mysql_real_escape_string(Anyimmediateactions).", HazardorNearMiss, Primarycause, Rottcause, Behaviours, PotentialOutcome, Likelihood, Comments, PersonsMostLikelyInjured, CloseDate, HighPotentialIncident, HPIInvestigatedBy, BUHSELeader, BUSectorLeader, HPIInvestigationConclusion, HPIBriefingNoteRef, HPIInvestigationCloseOutDate FROM hazzard WHERE ".$sqlstar." ORDER by DateOccured ASC";

$result = preg_replace('#(<br[ ]?[\\/]?>[\\r\
]*)+#',' ',$x);

$_SESSION['currentQuery5'] = $result;
6

Ok, so you have a SELECT so, you’ll get those details into PHP.
Why don’t you just make the replace into PHP?

" select ... ".REPLACE(NearMissDetails, "  ", " ")." ... WHERE "

This is not a valid code. There is no function named REPLACE in PHP. Also, your NearMissDetails will be a constant and in case it’s not defined, you’ll just get NearMissDetails, as string.
same invalid code here

"SELECT col1, col2, ".mysql_real_escape_string(Anyimmediateactions)." FROM ..."

So, to make it from SQL, you’d need something like:

 $x="select
Name,
Employerofperson,
Contactphone,
Location,
DATE_FORMAT(DateOccured, '%e-%c-%Y %H:%i') as DateOccurred,
ProductionZone,
REPLACE( NearMissDetails, '  ', ' ' ) NearMissDetails,
Anyimmediateactions,
HazardorNearMiss,
Primarycause,
Rottcause,
Behaviours, PotentialOutcome, Likelihood, Comments, PersonsMostLikelyInjured,
CloseDate, HighPotentialIncident, HPIInvestigatedBy, BUHSELeader, BUSectorLeader,
HPIInvestigationConclusion, HPIBriefingNoteRef, HPIInvestigationCloseOutDate

FROM hazzard
WHERE ".$sqlstar."
ORDER by DateOccured ASC";

Here, the $sqlstar is the security concern, not Anyimmediateactions (that cannot have mysql_real_escape_string because it’s still into your MySql)

7

Hi,

Yes this is selecting what it needs from the database to be exported to a csv file. The problem was highlighted when the client uploaded the exported csv file to their software and a question mark appeared where the line break was and that stopped it being uploaded to the software.

She then had to go through all the excel file taking out the spaces manually.

So ideally I need to clear these large spaces whether at the start of the string, the middle or the end.

Thanks for getting back to me though

8

Here we go I got it thankfully…


REPLACE(REPLACE(REPLACE(NearMissDetails, CHAR(13),' '), CHAR(10), ' '), '   ',' ') as NearMissDetails