Avatar of SambaCor
SambaCor
 asked on

How to romove non-ASCII Characters such as "#" from an Address field in access query?

I would like to remove (or replace) characters like "#" in the address field of my query. Some addresses look like this, 101 FRONT ST, #17 or 232 CANADA VALLEY RD APT #1 or 325 HESPERIAN BLVD #306...
The Address field already has these codes:
UCase(Left([Address],28))  & String(28-Len(Left(Nz([Address],""),28))," ") AS Ads

How can I modify the codes to include the removal (or replacement) of the character "#" for example?
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
SambaCor

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

One way

SELECT Replace([FIELD1],"#","") AS Expr1
FROM Table1;


mx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Update query (make a backup first)

UPDATE Table1 SET Table1.FIELD1 = Replace([FIELD1],"#","");


mx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

And within reason, you can string Replace's together.

UPDATE Table1 SET Table1.FIELD1 = Replace(Replace(Replace([FIELD1],"#",""),"$",""),"?","");


mx
Your help has saved me hundreds of hours of internet surfing.
fblack61
SambaCor

ASKER
I need to run this query once a month. Do I have to update the query every time after I need to run it?
Because the addresses are entered this way (with ASCII characters) by the data entry people, and that can't change.

Thanks!
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Are you wanting to Update a table based on a query, or ... just display the address w/o the special characters in a query?

mx
SambaCor

ASKER
just display the address w/o the special characters in a query.

Thanks!

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
barlet

i guess you will have to run this query every time you need to show the data without '#' sign since data is entered with '#' every time!

SELECT REPLACE(table_field,'#','') from table

Open in new window

SambaCor

ASKER
How can I include this Replace statement into the following codes?
UCase(Left([Address],28))  & String(28-Len(Left(Nz([Address],""),28))," ") AS Ads

Thanks!

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

barlet: Just curious, is the some reason you posted the same thing as I did in my first post ?

mx
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
barlet

ops I saw you Update post first.. sorry missed the first! totally my mistake
SambaCor

ASKER
So...How can I include the Replace statement in my code?

Thanks!
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

If you mean here:

UCase(Left([Address],28))  & String(28-Len(Left(Nz([Address],""),28))," ") AS Ads

then I guess - and just for the # ... to keep it simple now.

UCase(Left( Replace([Address],"#","")  ,28))  & String(28-Len(Left(Nz( Replace([Address],"#","")  ,""),28))," ") AS Ads
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jacques Bourgeois (James Burger)

Just for the record. # is an ASCII character.
Anthony Perkins

Or to be more precise there are no non-ASCII characters.  There are characters that cannot be represented with ASCII, but that is another story and I digress ... :)

Incidentally, this question has nothing to do with MS SQL Server, right?  I just thought I would check as you have included that zone.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

:-;
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Andrei Fomitchev

You can create trigger on the table and put there desired reaction (it is quite popular approach).
1. Raise Error
2. Replace # with empty string
3. Replace # with "No".

It will work at once when some address INSERTED/UPDATED. You will newer see # again.

You can verify if NEW value ON INSERT or UPDATE does not meet business rule and raise error with the message like "char # is not allowed in address". It will make users to use proper format and you will have no complaints that something went wrong because you changed the address somehow.

-----------------------
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
   AFTER INSERT, UPDATE
AS
BEGIN
      SET NOCOUNT ON;
        DECLARE @Address VarChar(100)
        SELECT TOP 1 @Address = address FROM inserted
        IF  PATINDEX('#', @Address) > 0
        RAISERROR (N'Wrong symbol # in address.',
           10, -- Severity,
           1, -- State);
END
GO
SambaCor

ASKER
This is how I did it from the qyery design view:

Ads: Replace(Replace(UCase(Left([Address],28)) & String(28-Len(Left(Nz([Address],""),28))," "),"#","No."), "1/2", "0.5")

It works!

So I would like to give the points to DatabaseMX.

Thank you all for helping!
SambaCor

ASKER
I would like to close this question now and thanks every one who participate on it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.