Solved

Getting rid of blank spaces in a field in SQL

Posted on 2007-03-31
6
2,122 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
DevOps Toolchain Recommendations

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

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

20 Experts available now in Live!

Get 1:1 Help Now