473,750 Members | 2,551 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Nested statements using Update

13 New Member
I am using the following statement to replace address abbreviations. I would like to expand this to look at multiple abbreviations which would be replace with the same word (ex: ST, ST., STR would all be replace with STREET). Right now I have indivisual statements for each abbreviation.

Expand|Select|Wrap|Line Numbers
  1. UPDATE Address SET Address.PHYSICAL_ADDRESS = Replace([PHYSICAL_ADDRESS]," ST "," STREET ")
  2. WHERE (((Address.[PHYSICAL_ADDRESS]) Like "* ST*"));
Apr 24 '12
10 2025
Rabbit
12,516 Recognized Expert Moderator MVP
If it's not followed by anything, you can append a space to the end yourself. There's not much you can do about st mary st unless you were willing to learn and use regular expressions. I don't know what you mean by last, I'm not aware of any such function. If you mean replace just the last occurrence, you could do that.
May 2 '12 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

5
3518
by: Jimmy Tran | last post by:
Hello, I am doing a bmw tracking project for school using asp and access2000. on my search.asp page, I can search for a particular bmw and order it if i want. It works fine when i do select query to get data. But when I try to order a bmw(using update query), it gives me this message error: Error Type: Microsoft JET Database Engine (0x80004005) Operation must use an updateable query. /bmw/search.asp, line 257
0
2406
by: Chris Hall | last post by:
The records in my database are displayed in a form as follows: %> <form action="report-ammend1.42.asp" method="post"name="form"> <table border=1> <% x = 1
2
945
by: DelphiBlue | last post by:
I have a Nested Datagrid that is using a data relations to tie the parent child datagrids together. All is working well with the display but I am having some issues trying to sort the child datagrid. HTML Datagrid1 TemplateColumn Table Header information Detail Information
4
1995
by: Profetas | last post by:
Hi, Nested statements helps to generate complex source code, what is the nesting limit that you normally consider? I know that the Halsted and McCabe analyses can define the acceptable level, but in your personnel point of views what is the limit before you re-code the block into multiple blocks a 3 level nesting example
4
1917
by: solidsna2 | last post by:
Hi, I am relatively new to SQL. I am using SQL 2000. I am trying to Update a field base in a criteria in a scond table. UPDATE Tbl1 SET Tbl1.Row2 = '1' WHERE Tbl1.Row1 = (SELECT Tbl1.Row1 FROM Tbl2, Tbl1
5
2200
by: cover | last post by:
I have an input form that passes data when submitted to a second form to let the user know what they have just entered into the db. My question comes with using 'update'. I'd like to query the database by equipment number (equipno is unique) and query all fields from that row, populating an original form 'look alike' whereby the user can add detail to the original records as the db builds. I'm using 'date' twice because I'd like to...
1
2501
by: arthy | last post by:
Hi, Is it possible to execute multiple statements on to the database using a single dbconnection object.what is the drawback in using .If not possible ,then how can the execution of multiple statements using a single object be done. thanks in advance, arthy
1
9714
by: nanoman | last post by:
Hello, I've got a Nested Set structure in MySQL 4 here with - id - lft - rgt - parent_id - root_id I wrote some test scripts and i discovered that the Nested Set (the
10
3571
by: Chris H | last post by:
Greetings, I'm trying to update an address field with "standard" abbreviations so that I can do a comparison of various accounts to one another on the address. I can update a set of records for "Road" to "Rd", but when I tried to stack the update clauses, I seem to get random updates within the file. All the updates are correct, but they're incomplete. Not sure how this needs to be done, I added a TOP statement but that didn't work. ...
3
1438
by: matthewaveryusa | last post by:
Hi, This is a simple question. How do I get statements that are nested (so like an if statement inside an if statement) to have the little +/- button so I can collapse it? thanks, Matt
0
9584
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9398
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9345
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9257
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6811
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6081
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4894
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2809
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2227
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.