Find and Replace

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Howdy..

I want to remove the string "CT" from a street name field. Is there a way to find and replace a value within a text string (i.e. remove "CT" from "Tyler Ct"... leaving just "Tyler".)
 
RichH said:
Howdy...

I want to remove the string "CT" from a street name field. Is there a way
Click to expand...
to find and replace a value within a text string (i.e. remove "CT" from
"Tyler Ct"... leaving just "Tyler".)

Try a query with something like:

SELECT Replace([FieldName],"CT","") AS Expr1
FROM TableName;

If that is satisfactory, run it as an update query, for a column that you
add to the table. When you are sure that that is satifactory, delete the old
column and rename the new one.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Howdy...

I want to remove the string "CT" from a street name field. Is there a way to find and replace a value within a text string (i.e. remove "CT" from "Tyler Ct"... leaving just "Tyler".)
Click to expand...

CAREFUL!

If you just replacet the text string ct in all cases, you'll convert
"Acton Ct" to "Aon".

I'd suggest an Update query:

UPDATE tablename
SET Street = Left([Street], Len([Street]) - 3)
WHERE Street LIKE "* CT";

The WHERE clause will select only those street names that have a 'ct'
added at the end; the blank between the wildcard and the ct will cause
it to ignore street names like Tesseract.
 
Back
Top