SQL Replace

[Chopper]

I've looked at BOL and through dejanews for a solution (similar post from
Lamine Darbouche on 30th July in this group) but cannot find a solution.
I am trying to replace text within a field using the SP below.

Alter Procedure cc_globalBodyReplace
(
@toReplace varchar(255),
@replaceWith varchar(255),
@result integer OUTPUT
)
AS
SET nocount on
BEGIN
-- Replace body
UPDATE PAGE_ATTRIBUTE
SET BODY = replace(BODY,@toReplace,@replaceWith)
WHERE BODY like '%'+@toReplace+'%'
END
--Return success/fail depending on sql feedback
IF @@Error = 0
select @result = 1
ELSE
select @result = 0

I am getting the following error when trying to save it:
'Argument data type text is invalid for argument 1 of replace function'
From BOL is suggests all arguments are strings but I've seen mention of it
being possible to use a column as the first argument.
I'm using SQL2K SP3a and Visual Studio 6.0 (SP5) as the editor.
The [BODY] column type is text.

TIA

chopper

[Bob Lehmann]

The error is just what it says it is. You need to change the type from text
to varchar. If that's not possible, then you can't use the replace function
in your SP.

Bob Lehmann

"Chopper" <chopper@despammed.com> wrote in message
news:3f9e86bd$0$252$cc9e4d1f@news.dial.pipex.com.. .

> I've looked at BOL and through dejanews for a solution (similar post from
> Lamine Darbouche on 30th July in this group) but cannot find a solution.
> I am trying to replace text within a field using the SP below.
>
> Alter Procedure cc_globalBodyReplace
> (
> @toReplace varchar(255),
> @replaceWith varchar(255),
> @result integer OUTPUT
> )
> AS
> SET nocount on
> BEGIN
> -- Replace body
> UPDATE PAGE_ATTRIBUTE
> SET BODY = replace(BODY,@toReplace,@replaceWith)
> WHERE BODY like '%'+@toReplace+'%'
> END
> --Return success/fail depending on sql feedback
> IF @@Error = 0
> select @result = 1
> ELSE
> select @result = 0
>
> I am getting the following error when trying to save it:
> 'Argument data type text is invalid for argument 1 of replace function'
> From BOL is suggests all arguments are strings but I've seen mention of it
> being possible to use a column as the first argument.
> I'm using SQL2K SP3a and Visual Studio 6.0 (SP5) as the editor.
> The [BODY] column type is text.
>
> TIA
>
> chopper
>
>

[Aaron Bertrand - MVP]

REPLACE is not a valid method for TEXT/NTEXT datatypes. See
[url]http://www.aspfaq.com/2445[/url]





"Chopper" <chopper@despammed.com> wrote in message
news:3f9e86bd$0$252$cc9e4d1f@news.dial.pipex.com.. .

> I've looked at BOL and through dejanews for a solution (similar post from
> Lamine Darbouche on 30th July in this group) but cannot find a solution.
> I am trying to replace text within a field using the SP below.
>
> Alter Procedure cc_globalBodyReplace
> (
> @toReplace varchar(255),
> @replaceWith varchar(255),
> @result integer OUTPUT
> )
> AS
> SET nocount on
> BEGIN
> -- Replace body
> UPDATE PAGE_ATTRIBUTE
> SET BODY = replace(BODY,@toReplace,@replaceWith)
> WHERE BODY like '%'+@toReplace+'%'
> END
> --Return success/fail depending on sql feedback
> IF @@Error = 0
> select @result = 1
> ELSE
> select @result = 0
>
> I am getting the following error when trying to save it:
> 'Argument data type text is invalid for argument 1 of replace function'
> From BOL is suggests all arguments are strings but I've seen mention of it
> being possible to use a column as the first argument.
> I'm using SQL2K SP3a and Visual Studio 6.0 (SP5) as the editor.
> The [BODY] column type is text.
>
> TIA
>
> chopper
>
>

[Chopper]

It's with hesitation I top post but here goes...
Many thanks all.
Will let you know how I get on.

chopper

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:%23VcjJEXnDHA.2432@TK2MSFTNGP10.phx.gbl...

> REPLACE is not a valid method for TEXT/NTEXT datatypes. See
> [url]http://www.aspfaq.com/2445[/url]
>
>
>
>
>
> "Chopper" <chopper@despammed.com> wrote in message
> news:3f9e86bd$0$252$cc9e4d1f@news.dial.pipex.com.. .

> > I've looked at BOL and through dejanews for a solution (similar post

from

> > Lamine Darbouche on 30th July in this group) but cannot find a solution.
> > I am trying to replace text within a field using the SP below.
> >
> > Alter Procedure cc_globalBodyReplace
> > (
> > @toReplace varchar(255),
> > @replaceWith varchar(255),
> > @result integer OUTPUT
> > )
> > AS
> > SET nocount on
> > BEGIN
> > -- Replace body
> > UPDATE PAGE_ATTRIBUTE
> > SET BODY = replace(BODY,@toReplace,@replaceWith)
> > WHERE BODY like '%'+@toReplace+'%'
> > END
> > --Return success/fail depending on sql feedback
> > IF @@Error = 0
> > select @result = 1
> > ELSE
> > select @result = 0
> >
> > I am getting the following error when trying to save it:
> > 'Argument data type text is invalid for argument 1 of replace function'
> > From BOL is suggests all arguments are strings but I've seen mention of

it

> > being possible to use a column as the first argument.
> > I'm using SQL2K SP3a and Visual Studio 6.0 (SP5) as the editor.
> > The [BODY] column type is text.
> >
> > TIA
> >
> > chopper
> >
> >

>
>

[Chopper]

Worked a treat!
Thanks again.

chopper

"Chopper" <chopper@despammed.com> wrote in message
news:3f9ead55$0$248$cc9e4d1f@news.dial.pipex.com.. .

> It's with hesitation I top post but here goes...
> Many thanks all.
> Will let you know how I get on.
>
> chopper
>
> "Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
> news:%23VcjJEXnDHA.2432@TK2MSFTNGP10.phx.gbl...

> > REPLACE is not a valid method for TEXT/NTEXT datatypes. See
> > [url]http://www.aspfaq.com/2445[/url]
> >
> >
> >
> >
> >
> > "Chopper" <chopper@despammed.com> wrote in message
> > news:3f9e86bd$0$252$cc9e4d1f@news.dial.pipex.com.. .

> > > I've looked at BOL and through dejanews for a solution (similar post

> from

> > > Lamine Darbouche on 30th July in this group) but cannot find a

solution.

> > > I am trying to replace text within a field using the SP below.
> > >
> > > Alter Procedure cc_globalBodyReplace
> > > (
> > > @toReplace varchar(255),
> > > @replaceWith varchar(255),
> > > @result integer OUTPUT
> > > )
> > > AS
> > > SET nocount on
> > > BEGIN
> > > -- Replace body
> > > UPDATE PAGE_ATTRIBUTE
> > > SET BODY = replace(BODY,@toReplace,@replaceWith)
> > > WHERE BODY like '%'+@toReplace+'%'
> > > END
> > > --Return success/fail depending on sql feedback
> > > IF @@Error = 0
> > > select @result = 1
> > > ELSE
> > > select @result = 0
> > >
> > > I am getting the following error when trying to save it:
> > > 'Argument data type text is invalid for argument 1 of replace

function'

> > > From BOL is suggests all arguments are strings but I've seen mention

of

> it

> > > being possible to use a column as the first argument.
> > > I'm using SQL2K SP3a and Visual Studio 6.0 (SP5) as the editor.
> > > The [BODY] column type is text.
> > >
> > > TIA
> > >
> > > chopper
> > >
> > >

> >
> >

>
>