String data manipulation and table update

[DougNaf]

I need to make changes to an address field. As an example an address needs to be changed from "123 CO RD J" to "123 COUNTY ROAD J" or "1234 SH 108" to "1234 STATE HIGHWAY 108"

IE change "CO RD" to "COUNTY ROAD" for all occurances in an address string. "CO RD" will NOT always be in the same position of the field.

I need to read the SQL record, manipulate the field and then write the new recalculated string back to the field.

Thanks in advance for suggestions.

Doug

 

[gmmastros]

That is exactly what the REPLACE function was designed for.

[tt][blue]
Select Replace(YourColumnName, 'CO RD', 'COUNTY ROAD')
From YourTable

[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try something like

declare @OriginalStr char(7), @NewStr char(13)
set @OriginalStr = ' CO RD '
set @NewStr = ' COUNTY ROAD '

select replace (' ' + Ltrim(rtrim(AddressField)) + ' ', @OriginalStr, @NewStr))

Assuming we're dealing with the character field.

 

[DougNaf]

The statement worked great.

Thanks!

 

[DougNaf]

I used the first example:
Select Replace(YourColumnName, 'CO RD', 'COUNTY ROAD')
From YourTable

Then ran the statement:

Select Replace(YourColumnName, 'ST RT', 'STATE ROUTE')
From YourTable

One of the original fields go back to CO RD that had been changed to COUNTY ROAD.

Is there some statement I need to include that actually writes the changes to the db?

Thanks

 

[gmmastros]

The simplest form is:

Update YourTableName
Set    YourColumnName = Replace(YourColumnName, 'ST RT','STATE ROUTE')

It may be faster to include a where clause so that only rows containing the search string are actually updated, like this:

Update YourTableName
Set    YourColumnName = Replace(YourColumnName, 'ST RT','STATE ROUTE')
Where  YourColumnName Like '%ST RT%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You need to use update command instead. But I would suggest my code instead of gmmastros in your case, so 'BOCO RD' would not become 'COUNTY ROAD', for example.

In my code change select replace to

update Address set AddressField = rtrim(ltrim(replace(' ' + AddressField+ ' ',@OldStr, @NewStr)))