Solved

Delete spaces from field

Posted on 2006-06-27
2
277 Views
Last Modified: 2011-10-03
I need to be able to run a query that deletes all spaces contained within a field.  The table is tblSymbol and has a field symCode.  

Field symCode contains a mixtureof alpha characters and spaces.  I need to delete all spaces from within the field.  lets say I have someting like

AA B
CD R
F GG
G H D
FFD (space) (space) etc

I need to end up with

AAB
CDR
FGG
GHD
FFD (with no following spaces)  in fact no following spaces for any of the symbols in the field.

need a quick solution here, thanks

Any suggestions
thanks
0
Comment
Question by:sarahgal
[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
2 Comments
 
LVL 16

Accepted Solution

by:
GreymanMSC earned 500 total points
ID: 16998566
Use the following sql statement to update the table

  Update tbltblSymbol set symCode=Replace(symCode, " ", "") where InStr(1, symCode, " ")>0;

Use the following to simply retrieve the values stripped of spaces,

  Select Replace(symCode, " ", "") as symCodeDespaced from tbltblSymbol;
0
 

Author Comment

by:sarahgal
ID: 16998666
the update statement works perfectly.  thanks GreymanMSC.

appreciate the quick response....
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

628 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