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
Solved

Getting rid of blank spaces in a field in SQL

Posted on 2007-03-31
6
2,129 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
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…

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