Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-06-11
2
Medium Priority
?
934 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

636 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