[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MySQL trim?

Posted on 2014-01-28
8
Medium Priority
?
535 Views
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?

Thanks
0
Comment
Question by:Richard Korts
[X]
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
8 Comments
 
LVL 4

Assisted Solution

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

Open in new window

0
 
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 4

Expert Comment

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

Expert Comment

by:snoyes_jw
ID: 39816227
MySQL also has LTRIM() and RTRIM().
0
 
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.
0
 
LVL 111

Accepted Solution

by:
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.
0
 

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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

650 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