• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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