Replace substring in field

Hi
I am trying to figure a way of replacing occurances of the following text: "a good years work" with the (corrected): "a good year's work". The substring exists within a variety of sentences eg "Paul has produced a good years work", "Quite simple a good y
ears work". Basically the users have missed the apostrophe! The field is a column in a sql table - I guess I need some kind of update query method
Thanks
Eddie
Hi,
There are 2 approaches, 1 is just display with "a good year's work"
set quoted_identifier off
select replace(field_name,'a good years work',"a good year's work") from
table_name
2nd approach is to to update the values inside the table
set quoted_identifier off
update table_name
set field_name=replace(field_name,'a good years work',"a good year's work")
select field_name from table_name
Thanks
Hari
MCDBA
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:59B4865C-7C36-433C-865A-85A73CCC2C1A@.microsoft.com...
> Hi
> I am trying to figure a way of replacing occurances of the following text:
"a good years work" with the (corrected): "a good year's work". The
substring exists within a variety of sentences eg "Paul has produced a good
years work", "Quite simple a good years work". Basically the users have
missed the apostrophe! The field is a column in a sql table - I guess I need
some kind of update query method
> Thanks
> Eddie

Replace substring in field

Hi
I am trying to figure a way of replacing occurances of the following text: "
a good years work" with the (corrected): "a good year's work". The substrin
g exists within a variety of sentences eg "Paul has produced a good years wo
rk", "Quite simple a good y
ears work". Basically the users have missed the apostrophe! The field is a
column in a sql table - I guess I need some kind of update query method
Thanks
EddieHi,
There are 2 approaches, 1 is just display with "a good year's work"
set quoted_identifier off
select replace(field_name,'a good years work',"a good year's work") from
table_name
2nd approach is to to update the values inside the table
set quoted_identifier off
update table_name
set field_name=replace(field_name,'a good years work',"a good year's work")
select field_name from table_name
Thanks
Hari
MCDBA
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:59B4865C-7C36-433C-865A-85A73CCC2C1A@.microsoft.com...
> Hi
> I am trying to figure a way of replacing occurances of the following text:
"a good years work" with the (corrected): "a good year's work". The
substring exists within a variety of sentences eg "Paul has produced a good
years work", "Quite simple a good years work". Basically the users have
missed the apostrophe! The field is a column in a sql table - I guess I need
some kind of update query method
> Thanks
> Eddie

Replace on a text field.

I need to search for all occurances of particular string within a column on a table. The column has a data type of Text. It will not allow me to use the replace function on a Text field only on varchars or chars. Does anybody have any ideas of how I can do this?njjones,

Look at Full Text Indexing in Books Online (BOL).

If however you can guarantee that none of the fields exceed 8000 characters you could CAST the TEXT field to a VARCHAR(8000). ie.

REPLACE(CAST(yourtextcolumn AS VARCHAR(8000)),'ABC','DEF')

macka.|||I have done that however quite a lot of the fields I need to affect are greater than 8000 characters (hence using the text datatype). I wanted to run a query to find out how many were longer but you can't use Len on a text field either - is there an easy way of finding the character length of text in a text column?|||Chances are not many will be exactly 8000 in length, so the following query gives you a rough idea of how many are bigger than 8k, but truncating all fields to 8000 characters.

SELECT COUNT(*)
FROM yourtable
WHERE LEN((CAST(yourtextcolumn AS VARCHAR(8000)))) = 8000

macka.|||The following works - seems a little heavy handed for a replacement of a one line function, but any:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%25.224.8.30%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%25.224.8.30%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 11
'modconnect1.qinetiq.r.mil.uk'
select @.pos1 =
patindex('%25.224.8.30%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||njjones,

Did you ever get the 'replace' issue resolved in a Text field? I need to do a similar action, finding all the commas in a text field and replacing it with a semi-colon.

Thanks.|||The answer is above, however I have recopied and pasted it below and updated it so that it should work for , and ; - probably could have parameterised this and turned it in a user defined function but it is not something I have needed to do often enough to bother with:

declare datacursor cursor
for
select
dataid, TEXTPTR(description)
from
tbl_data
where
description like '%,%'
declare @.ptrval binary(16)
declare @.dataid int
declare @.pos1 int
open datacursor
fetch next from datacursor
into @.dataid, @.ptrval
while @.@.fetch_status = 0
begin
select @.pos1 = patindex('%,%',tbl_data.description) from
tbl_data where dataid = @.dataid
while @.pos1 <> 0
begin
set @.pos1 = @.pos1-1
updatetext tbl_data.description @.ptrval @.pos1 1
';'
select @.pos1 =
patindex('%,%',tbl_data.description) from tbl_data where dataid =
@.dataid
end
fetch next from datacursor
into @.dataid, @.ptrval
end
close datacursor
deallocate datacursor|||Nicky, thanks. A couple of quick modifications and I had this working well for my table. I appreciate it.

RY

Replace Function? SQL2K/EM

Hi,
I need to change some table and field names - is there a way to update
all the occurances in Views and Stored procedures?Update the source code for the views and stored procedures and then redeploy
them. You can use the rows from sysdepends to determine which views and
stored procs are affected by name change of a table.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to change some table and field names - is there a way to update
> all the occurances in Views and Stored procedures?
>|||Do they have to be updated manually?
Visual Studio has tools to do this automatically, does EM have nothing
similar for its source code ?
Tim Dot NoSpam wrote:
> Update the source code for the views and stored procedures and then redepl
oy
> them. You can use the rows from sysdepends to determine which views and
> stored procs are affected by name change of a table.
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...|||> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
EM/SSMS will not automatically rename objects because there is nothing on
the server side that will track dependencies. However, this one of the many
new features included in the upcoming Visual Studio 2005 Team Edition for
Database Professionals.
See http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150110493.525079.29150@.i40g2000cwc.googlegroups.com...
> Do they have to be updated manually?
> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
> Tim Dot NoSpam wrote:
>