Solved

remove padding from varchar fields

Posted on 2006-06-14
3
1,184 Views
Last Modified: 2008-02-01
I just realized that I have a database with a few thousand records with unnecessary padding spaces in the fields fields.  I had to export from one DB then upload to MySQL, and didn't realize that the field values were exported with padding spaces.  

Anyway,  "Smith, John" is coming back from MySQL as "Smith            , John               ".  I put the results straight into HTML, so the extra spaces are ignored, and querying the DB with "Smith" still works . . . but I'd still like to clean up the fields.

Can anyone come up with an SQL statement I could plug into MySQL to strip the unecessary padding from the fields?  Unfortunately, I'm much more experienced with MS Access which, for all its shortcomings, has a handy expression builder for putting together scripts to do this kind of thing.
0
Comment
Question by:Zeek0
  • 2
3 Comments
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16906296
Hi Zeek0,

if the first and last name come from different columns, wrap each column name in TRIM(columnname).

Cheers!
0
 
LVL 30

Accepted Solution

by:
todd_farmer earned 500 total points
ID: 16906309
UPDATE table_name SET first_name=TRIM(first_name), last_name=TRIM(last_name);

would clean up existing entries.
0
 

Author Comment

by:Zeek0
ID: 16906404
Thanks.  I should have been able to figure that out myself, but you saved me some time sifting through help files/websites. :)
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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