• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1033
  • Last Modified:

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

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
Tom Winslow
Asked:
Tom Winslow
1 Solution
 
Kent DyerIT Security Analyst SeniorCommented:
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
 
Tom WinslowAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now