Solved

Getting rid of blank spaces in a field in SQL

Posted on 2007-03-31
6
2,117 Views
Last Modified: 2012-05-05
How do i get rid of spaces in the SQL script for the Fullname field?
The SQL is:

SELECT u.UserName as USER, u.FullName as full, u.Postcode AS pcode_orig, LEFT(u.Postcode, LENGTH(u.Postcode) - LOCATE(' ', REVERSE(u.Postcode))) as pcode, u.Tel as Tel, u.Email AS Email, u.Address AS Address, c.CustomerID FROM dict8.Customer c JOIN blabla.`User` u  ON c.UserID = u.UserID WHERE u.Postcode IS NOT NULL GROUP BY pcode"
0
Comment
Question by:sebastiz
6 Comments
 

Author Comment

by:sebastiz
ID: 18829678
For example turing "Simon Travis Junior" to "SimonTravisJunior"
0
 
LVL 4

Accepted Solution

by:
mukhtar2t earned 500 total points
ID: 18830734
select replace('Simon Travis Junior',' ','')
0
 
LVL 4

Expert Comment

by:mukhtar2t
ID: 18830761
To get the first word before the first space for you Postcode you can use
LEFT(u.Postcode,InStr(u.Postcode,' ')) instead of LENGTH(u.Postcode) - LOCATE(' ', REVERSE(u.Postcode)))
0
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

 
LVL 4

Expert Comment

by:mukhtar2t
ID: 18830769
Sorry
LEFT(u.Postcode,InStr(u.Postcode,' ')-1)
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 18836337
To remove spaces from a string, use REPLACE(str, ' ', '') to delete all spaces (or more accurately replace all spaces with empty strings).  You can use that in a SELECT, INSERT or UPDATE.  I'm not clear on where and when you want to change the strings.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18836517
SELECT u.UserName as USER, replace(u.FullName," ","") as full, u.Postcode AS pcode_orig, LEFT(u.Postcode, LENGTH(u.Postcode) - LOCATE(' ', REVERSE(u.Postcode))) as pcode, u.Tel as Tel, u.Email AS Email, u.Address AS Address, c.CustomerID FROM dict8.Customer c JOIN blabla.`User` u  ON c.UserID = u.UserID WHERE u.Postcode IS NOT NULL GROUP BY pcode"
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

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 …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
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: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

13 Experts available now in Live!

Get 1:1 Help Now