Solved

MySQL trim?

Posted on 2014-01-28
8
523 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 167 total points
ID: 39815881
SELECT RTRIM(LTRIM(' LastName')) AS LastName;

Open in new window

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 39815941
0
 
LVL 59

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 166 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 110

Accepted Solution

by:
Ray Paseur earned 167 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
join tables 4 57
MySQL Memory Keeps Increasing 4 65
Uploading a CSV Data Import via PHP & MySql 3 58
How efficient to move databases to Azure? 5 58
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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