String replacement updates

[rrhandle]

I am frequently finding myself have to replace text in a table(s). I know there must be a better way to do this, so I am asking for suggests.

EXAMPLE: Replace 'TH-' in the ABBR_1 field with 'TMF-'

Table name: ABBR

ABBR_1    PRICE    Desc
------    -----    ----
TH-34     12.34    3" thermaform
TH-12     15.45    1" thermaform
ER-T      12.00    T-Erector

Thank for the suggestions.

--Rob

 

[gmmastros]

First, you should run a 'select' query to make sure it's right.

Select Replace(Abbr_1, 'TH-', 'TMF-') As NewValue,
       *
From   Abbr

If it's right, then...

Update Abbr
Set    Abbr_1 = Replace(Abbr_1, 'TH-', 'TMF-')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cLFlaVA]

something like:

update ABBR
   set ABBR_1 = replace( ABBR_1, 'TH-', 'TMF-' )
[gray] where ABBR_1 like 'TH-%'[/gray]

the where clause is optional...it may speed up your query.



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[rrhandle]

Thanks for the help!

I also found this link:

http://www.tek-tips.com/viewthread.cfm?qid=986358,

but it looks like the way you recommend is simpler.

Thanks again,

--Rob

 

[SQLSister]

Remember that you need to be careful doing this (which is why running a select first was recommended by cLFlaVA) doing a global replace can break things if the pattern shows up on records you don't want to change.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[cLFlaVA]

gmmastros recommended the select first



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[gmmastros]

Pfffft. I like SQL Sister, so I didn't bother correcting her.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

sorry george didn;t mean to give someone else credit. And I'm glad you like me. I like you as well.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[cLFlaVA]

i like credit.



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]