• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

MySQL trim?

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?

Thanks
0
Richard Korts
Asked:
Richard Korts
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
ravikantninaveCommented:
SELECT RTRIM(LTRIM(' LastName')) AS LastName;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
@ravikantninave, LTRIM() and RTRIM() are Microsoft SQL Server functions.  For MySQL, the function is TRIM() as snoyes_jw referenced.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ravikantninaveCommented:
Oh Yes! I didn't see it.  I thought it was Sql Server.
0
 
snoyes_jwCommented:
MySQL also has LTRIM() and RTRIM().
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Ray PaseurCommented:
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.
0
 
Richard KortsAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now