Solved

Getting rid of blank spaces in a field in SQL

Posted on 2007-03-31
6
2,125 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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 …
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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