MySQL - Trim whitespaces & line breaks?

Posted on 2009-02-20
Last Modified: 2012-05-06
First, is is possible to trim line breaks from fields in my table?
Second, if I use trim, will it remove *all* the whitespace at the end of a field?  In some cases, it looks like the data has multiple spaces at the end, and I want to be certain that I won't have to run TRIM repeatedly?
Question by:n00b0101
    1 Comment
    LVL 39

    Accepted Solution

    Yes, you can trim line breaks.

    No, trim() will not remove all whitespace. It will only remove one type of whitespace at a time: either spaces or line breaks. It also differs between unix newline and windows crlf.

    The syntax for trim() is:

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr  FROM] str)

    You can do any of these:

    TRIM(TRAILING '\n' from column)  
    TRIM(TRAILING '\r\n' from column)
    TRIM(TRAILING ' ' from column)

    ...but you can't do them all in combination, as far as I know. You would have to run them all repeatedly, untill all whitespace is gone. It might be more efficient to use a script.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now