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 SUBSTRING BETWEEN TWO CHARACTERS

Hi.
I have to replace substrings which are between two characters.
For example: I have to replace or remove all the characters that are
between ' \':
So, i have the string abcde\fgh\ikl and i want to get: abcdeikl.
I use the following select statement:
REPLACE(ColumnName, SUBSTRING([ColumnName, CHARINDEX('\', ColumnName),
CHARINDEX('\', ColumnName, CHARINDEX('\', ColumnName) + 1) -
CHARINDEX('\', ColumnName + 1), '')
Is there any more efficient way?
Thanks.Hi
declare @.d varchar(50)
set @.d='abkcde\fgh\ikl'
select STUFF(@.d,start,endpos-start,'')
from
(
select charindex('',@.d,1) as start,
len(@.d)-charindex('',reverse(@.d),1)+2 as endpos
) as der
<stelioshalkiotis@.yahoo.gr> wrote in message
news:1132833870.358155.145790@.g47g2000cwa.googlegroups.com...
> Hi.
> I have to replace substrings which are between two characters.
> For example: I have to replace or remove all the characters that are
> between ' ':
> So, i have the string abcde\fgh\ikl and i want to get: abcdeikl.
> I use the following select statement:
> REPLACE(ColumnName, SUBSTRING([ColumnName, CHARINDEX('', ColumnName),
> CHARINDEX('', ColumnName, CHARINDEX('', ColumnName) + 1) -
> CHARINDEX('', ColumnName + 1), '')
> Is there any more efficient way?
> Thanks.
>|||or
declare @.d varchar(50)
set @.d='abkcde\fgh\ikl'
Select
Substring(@.d,1,charindex('\',@.d)- 1)+reverse(Substring(reverse(@.d),1,chari
nde
x('\',reverse(@.d))-1))
Madhivanan

Replace substring

Hello
Need to replace text_A with text_B contained within 2 columns of table., SQL
server 2000.
table name = stock
column names = description and long_description
Thanks for any help
Regards
Maxwellmaxbrit wrote:

> Need to replace text_A with text_B contained within 2 columns of
> table., SQL server 2000.
> table name = stock
> column names = description and long_description
update stock set description = replace(description, 'text_A',
'text_B'), long_description = replace(long_description, 'text_A',
'text_B'),
HTH,
Stijn Verrept.|||Many thanks for quick reply.
Merry Christmas and a Happy New Year.
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:IdydnaFpC_7QajrenZ2dnUVZ8qWdnZ2d@.sc
arlet.biz...
> maxbrit wrote:
>
> update stock set description = replace(description, 'text_A',
> 'text_B'), long_description = replace(long_description, 'text_A',
> 'text_B'),
> --
> HTH,
> Stijn Verrept.