SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Code:
    $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. #2
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have tried another way now, and still no cigar.

    I changed it to:

    Code:
    ".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. #3
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thought I'd give it a go outside the select statement.

    Code:
    $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.

    Code:
    $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.

    Code:
    $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. #4
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have gone back to the select statement and tried:

    Code:
    TRIM(both '\r\n' 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. #5
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    $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\n]*)+#',' ',$x);
    
    $_SESSION['currentQuery5'] = $result;

  6. #6
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Ok, so you have a SELECT so, you'll get those details into PHP.
    Why don't you just make the replace into PHP?

    Code:
    " 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
    Code:
    "SELECT col1, col2, ".mysql_real_escape_string(Anyimmediateactions)." FROM ..."
    So, to make it from SQL, you'd need something like:
    Code:
     $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)
    Be nice to nerds. Chances are you'll end up working for one - Bill Gates
    > photos | admin panel

  7. #7
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here we go I got it thankfully...

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •