Update (abbreviate) field data

[rwies]

I need a transact SQL routine that will allow me to edit (update) an address field. For example if the address in PremiseAddress is 1234 East 1st Avenue Suite 100, I need to edit it to be 1234 E 1st AV Ste 100.

As you can see I need to abbreviate East, Avenue and Suite. We are converting data from one database to another. Unfortunately our new database has a shorter field for address.

Do any of you know how I can accomplish this?

Thanks,

Ron--

 

[donutman]

First look for a new database. Any db that can't store varchar(50) suwxyzs, even if it's a PDA.

Second, write standard Update statements that do things like:

[Blue]UPDATE[/Blue] YourTable
   [Blue]SET[/Blue] YourAddress[Gray]=[/Gray][Fuchsia]Replace[/Fuchsia][Gray]([/Gray]YourAddress[Gray],[/Gray][red]'Avenue'[/red][Gray],[/Gray][red]'Ave'[/red][Gray])[/Gray]
   [Blue]WHERE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]YourAddress[Gray])[/Gray][Gray]>[/Gray]20 [green]--or whatever
[/green][Blue]SELECT[/Blue] YourAddress [Blue]FROM[/Blue] YourTable [Blue]WHERE[/Blue] [Fuchsia]Len[/Fuchsia][Gray]([/Gray]YourAddress[Gray])[/Gray][Gray]>[/Gray]20[green]
--In order to see what more work needs to be done[/green]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[rwies]

I should have been more specific. I have a database with about 20,000 rows. I need a transact SQL routine that will go through the address field of each row and evaluate the contents. I need to look for a direction (North, South, East, or West), a street type (Avenue, Street, Place, or Drive) and for Suite. If these exist I need to update the field and replace North with N, South with S, Avenue with AV, Suite with Ste, etc.

This may be a cursor, I’m not sure.

Thanks,

Ron--

 

[donutman]

If the objective is to reduce the length of the address field, there is no point in doing all kinds of work that doesn't need to be done. Consider doing the approach I suggest, before you move the data. Only correct the addresses that you need to and don't try to do it in one step.
DON'T EVEN CONSIDER A CURSOR!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

In this case I would use a while loop, but not against the main table. I would create a table to store the long word and the abrreviations you want.

Then I would loop though this table and insert the long word and the abbrevaition into variables, then run the set-based UPdate statement above withthe variables in the replace, then loop to the next record of the abbreviation table and run the Update statement again.

Alternatively you could write a separate update for each word you want to replce. This would be the most efficent in terms of running time, but in this instance, since you only need to make the conmversion once, I think the other would be more efficient to build and not that much slower to run.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief