Solved

MySQL trim?

Posted on 2014-01-28
8
516 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 4

Assisted Solution

by:ravikantninave
ravikantninave earned 167 total points
Comment Utility
SELECT RTRIM(LTRIM(' LastName')) AS LastName;

Open in new window

0
 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
@ravikantninave, LTRIM() and RTRIM() are Microsoft SQL Server functions.  For MySQL, the function is TRIM() as snoyes_jw referenced.
0
 
LVL 4

Expert Comment

by:ravikantninave
Comment Utility
Oh Yes! I didn't see it.  I thought it was Sql Server.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 33

Expert Comment

by:snoyes_jw
Comment Utility
MySQL also has LTRIM() and RTRIM().
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 166 total points
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 167 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MySQL 11 66
MySQL - Limit or Top Records 15 36
phpMyAdmin simple sql statement 3 38
Mysql Crashing Intermittently 16 36
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

14 Experts available now in Live!

Get 1:1 Help Now