Global Replace # sign (Pound Sign)

[Cydney]

Hi. I need to globally replace a comma and a pound sign in a table to show a
comma, then space, then pound sign. Because the # sign is reserved to
indicate a number, I can't seem to do that. How can I make this global
replacement?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[John W. Vinson]

Hi. I need to globally replace a comma and a pound sign in a table to show a
comma, then space, then pound sign. Because the # sign is reserved to
indicate a number, I can't seem to do that. How can I make this global
replacement?

If you're using the criterion in a LIKE clause, you must enclose the wildcard
# (which is a wildcard for a numeric digit) in square brackets:

UPDATE fieldname
SET fieldname = Replace([fieldname], "#", ", #")
WHERE fieldname LIKE "*[#]*";

The WHERE clause will find records containing a literal # character in the
field; the SET clause will call the Replace function to replace

#

with

, #

 

[Klatuu]

You can use an update query that includes the Replace function to change the
value of a field. Here is an example you can use. Just change the table and
field names>

UPDATE tblClient SET tblClient.MainName = Replace([MainName],",#",", #");

 

[Cydney]

Actually.. I was using Global Replace wizard (Ctrl-H) to find and replace the
characters. But I used your example and came upon a discovery.

If you type in the Find: ,[#]
And in the Replace With: , #
And select "Match Any Part of Field" ...
....it will globally replace just the way I need it done without disturbing
the other numbers and characters in the field.

Thank you for the inspiration, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

Hi. I need to globally replace a comma and a pound sign in a table to show a
comma, then space, then pound sign. Because the # sign is reserved to
indicate a number, I can't seem to do that. How can I make this global
replacement?

If you're using the criterion in a LIKE clause, you must enclose the wildcard
# (which is a wildcard for a numeric digit) in square brackets:

UPDATE fieldname
SET fieldname = Replace([fieldname], "#", ", #")
WHERE fieldname LIKE "*[#]*";

The WHERE clause will find records containing a literal # character in the
field; the SET clause will call the Replace function to replace

#

with

, #