Solved

Getting rid of blank spaces in a field in SQL

Posted on 2007-03-31
6
2,132 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
[X]
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
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot table 2 45
SQL Query help 3 25
T-SQL: How to extract records into a new table 7 23
Need to create and populate a column map table 5 27
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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