?
Solved

MSAccess query design to Search and Replace "#" (pound sign)

Posted on 2013-06-11
2
Medium Priority
?
875 Views
Last Modified: 2013-06-13
I have a JD Edwards table where an upload caused extra data to be added to two (2) description fields. I need to search for 'MSA#350298' on the description fields and replace with '' (nothing). I can search for the pound sign by using brackets. *[#]* but I cannot search for the entire string.

My plan is to use the search and replace feature in Microsoft Access. Is there a better process?

tw
0
Comment
Question by:Tom Winslow
[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 17

Accepted Solution

by:
Kent Dyer earned 2000 total points
ID: 39239932
How about using Chr(35) ?  You should be able to do something like this..
UPDATE TABLE
SET SOMEFIELD = REPLACE(SOMEFIELD,CHR(35),"")
WHERE SOMEFIELD = SOMEVALUE

Open in new window


HTH,

Kent
0
 

Author Comment

by:Tom Winslow
ID: 39240887
The field I have to delete is " OGGLE MCM #5071A58"  and it has a leading space. there are over 17k description lines that contain this 'trash' data that I have to remove.

SAMPLE DATA:

PROC TO SECURE 3 JAW CPLGS TO SHAFT OGGLE MCM #5071A58
FEED WDO REPLACEMENT PROC 2.4M VISAT & 3.8M DPC/DPK OGGLE MCM #5071A58
PROCESS SPEC F/BONDING SILI- CONE RBR TO AL & SS OGGLE MCM #5071A58
ANT INSTL 3.8DPC/K  OGGLE MCM #5071A58
ANT HARWARE KIT 3.8M (STD)  OGGLE MCM #5071A58
HDW KIT PIPE MT INSTL 3.8M DPC/K OGGLE MCM #5071A58
REF INSTL 3.8M HDW KIT STD  OGGLE MCM #5071A58
REF INSTL 3.8M BOX 2 RADIAL BE AM + MISC OGGLE MCM #5071A58
REF INSTL 3.8M BOX 3 PNL ASSY  OGGLE MCM #5071A58
PNL 3.8M  OGGLE MCM #5071A58
PNL 3.8M PTD  OGGLE MCM #5071A58
PNL 3.8M  OGGLE MCM #5071A58
PNL 3.8M PTD  OGGLE MCM #5071A58
PNL ASSY 3.8M  OGGLE MCM #5071A58
PNL ASSY 3.8M PTD  OGGLE MCM #5071A58
PNL ASSY 3.8M  OGGLE MCM #5071A58
PNL ASSY 3.8M PTD  OGGLE MCM #5071A58
PNL SKIN TRIMMED TO SZ 3.8M  OGGLE MCM #5071A58
PNL SKIN TRIMMED TO SZ 3.8M  OGGLE MCM #5071A58
PNL SKIN STRETCH FORMED 3.8M  OGGLE MCM #5071A58
PNL 3.8M  OGGLE MCM #5071A58
PNL 3.8M PTD  OGGLE MCM #5071A58
PNL 3.8M  OGGLE MCM #5071A58
PNL 3.8M PTD  OGGLE MCM #5071A58

I need to capture all of the data and delete it including the spaces.

tw
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

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