Using REPLACE Function in SQL Server 2000
I have a table
in a sql 2000 database which contains email distribution lists.
I want to create a stored procedure that accepts an email address as an input and then remove all entries from the List column for that email address….
I can find the individual emails to be deleted by using Lowfatspread's code
Declare @email varchar (20)
set @email = Jsmith@acme.com’
select List from DISTRIB_LISTS where List LIKE '%'+@email+'%'
I now want to delete the specified entries from the
column rows and am looking at the REPLACE Function
REPLACE
(List, --string to be searched
@email , --substring to be found
''--replacement string
)
when I run this code, i get the following error
: Incorrect syntax near 'REPLACE'.
any guidance appreciated, thanks
in a sql 2000 database which contains email distribution lists.
I want to create a stored procedure that accepts an email address as an input and then remove all entries from the List column for that email address….
I can find the individual emails to be deleted by using Lowfatspread's code
Declare @email varchar (20)
set @email = Jsmith@acme.com’
select List from DISTRIB_LISTS where List LIKE '%'+@email+'%'
I now want to delete the specified entries from the
column rows and am looking at the REPLACE Function
REPLACE
(List, --string to be searched
@email , --substring to be found
''--replacement string
)
when I run this code, i get the following error
: Incorrect syntax near 'REPLACE'.
any guidance appreciated, thanks
ASKER
and should also preface REPLACE with SELECT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For display
SELECT *,Replace(List,@email,'')R
FROM DISTRIB_LISTS
<<where condition>>
SELECT *,Replace(List,@email,'')R
FROM DISTRIB_LISTS
<<where condition>>
do you want something like this
Select REPLACE(List, @email , '') from DISTRIB_LISTS
what are the datatypes for LIST ?
Select REPLACE(List, @email , '') from DISTRIB_LISTS
what are the datatypes for LIST ?
ASKER
the below returns the List - Jsmith@acme.com’
now i need to delete these entries from the table
Declare @email varchar (20)
set @email = Jsmith@acme.com’
select replace
( List,
@email,
''
)
from DISTRIB_LISTS where List LIKE '%'+@email+'%'
now i need to delete these entries from the table
Declare @email varchar (20)
set @email = Jsmith@acme.com’
select replace
( List,
@email,
''
)
from DISTRIB_LISTS where List LIKE '%'+@email+'%'
hi,
just for clarification,
when you say delete from table, you meant to delete row from table or you wanted to update that column...
for updating, I already showed you.......
just for clarification,
when you say delete from table, you meant to delete row from table or you wanted to update that column...
for updating, I already showed you.......
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Declare @email varchar (20)
set @email = Jsmith@acme.com’
Update DISTRIB_LISTS
set List=replace( List,@email,'')
where List LIKE '%'+@email+'%'
set @email = Jsmith@acme.com’
Update DISTRIB_LISTS
set List=replace( List,@email,'')
where List LIKE '%'+@email+'%'
ASKER
Hi VipulKadia:
that will DELETE every row with 'Jsmith@acme.com’ ?
I just want to remove the 'Jsmith@acme.com’ entries only...
Hi Brichsoft:
yes you are correct, I do not want to delete the row just update the column..
going through my old questions now to find your instructions, thanks
that will DELETE every row with 'Jsmith@acme.com’ ?
I just want to remove the 'Jsmith@acme.com’ entries only...
Hi Brichsoft:
yes you are correct, I do not want to delete the row just update the column..
going through my old questions now to find your instructions, thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi pratima_mcs and Brichsoft.
Declare @email varchar (20)
set @email = Jsmith@acme.com’
Update DISTRIB_LISTS
set List=replace( List,@email,'')
where List LIKE '%'+@email+'%'
this works ,thanks
Hi Vipulkadia, thanks for your input
Declare @email varchar (20)
set @email = Jsmith@acme.com’
Update DISTRIB_LISTS
set List=replace( List,@email,'')
where List LIKE '%'+@email+'%'
this works ,thanks
Hi Vipulkadia, thanks for your input
ASKER