A challenge - Find & Replace within a column

[plshelpme]

Here's what I want to do.

i have a table with multiple columns and in column B i have a list of names seperated by ^s

Here is a sample of records:

1   Bill^Sue^Bob^Frank^ 
2   Fred^Joe^Bill^John^Sue^Doug^
3   Joe^Sam^Todd^
4   Bill^

I want to write a script that will look within column B and if it finds the name 'Bill' it will replace it with 'William'.

So the above sample of records would become:

1   William^Sue^Bob^Frank^ 
2   Fred^Joe^William^John^Sue^Doug^
3   Joe^Sam^Todd^
4   William^

Any one have any idea how to do this?

There are several records and several orders in which the names could appear and I do not want to write multiple UPDATE scripts....I just want a 'find & replace' feature for within a column within and SQL table.

It HAS to be possible!!!

Thanks

 

[jhall156]

This should work. Create a recordset then iterate through it using the function
Replace(namefield.value,'valuetoreplace','replacementvalue') JHall

 

[MARKNLA2]

i tried the replace and substitute functions in an update's and append's criteria row. now i'm writing an expression based on iif.

where would the replace function be written? not the criteria row.

marknla2

 

[tlbroadbent]

marknla2,

I'm unclear what you want to accomplish. Please explain. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.