MySQL trim?

Posted on 2014-01-28
Medium Priority
Last Modified: 2014-01-29
I have a table with customer names, last & first are separate columns, In a number of instances, there is a blank space as the first character of the last name.

I'm doing a query like "SELECT * form customer order by lastname, firstname. Of course I get all the ones with blank spaces first.

Is there a function like trim where I can say something like "order by trim(lastname)..."?

Or what is the best way to do this?

Question by:Richard Korts
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2

Assisted Solution

ravikantninave earned 668 total points
ID: 39815881
SELECT RTRIM(LTRIM(' LastName')) AS LastName;

Open in new window

LVL 60

Expert Comment

by:Kevin Cross
ID: 39816210
@ravikantninave, LTRIM() and RTRIM() are Microsoft SQL Server functions.  For MySQL, the function is TRIM() as snoyes_jw referenced.
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Expert Comment

ID: 39816214
Oh Yes! I didn't see it.  I thought it was Sql Server.
LVL 33

Expert Comment

ID: 39816227
MySQL also has LTRIM() and RTRIM().
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 664 total points
ID: 39816677
Good point!  I think with the single TRIM capability of MySQL, it is unnecessary to use the two in combination though.  However, it is good to note the SQL Server syntax will work, creating cross-platform compatibility.
LVL 111

Accepted Solution

Ray Paseur earned 668 total points
ID: 39818257
Maybe it would be worth sanitizing the data with a SELECT and UPDATE after trimming the fields.  Just a thought...

Going forward, PHP has a trim() function that will help you ensure that there is no whitespace padding in the data sent to the data base.

Author Closing Comment

by:Richard Korts
ID: 39818314
I did as Ray suggested; it was MUCH easier.

Actually, the data is being transferred from a MS Access database, I did the trim in php in the upload program.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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