How to remove the data from the record

• sk.panda

  Right there with Babe

  Points: 796

  More actions

  November 11, 2009 at 11:13 pm

  #136905

  Dear All,

  I have a table called EMailNotify.

  To_Address is a column which contains email address.

  The record should be present the column like

  a.b.ssl@test.com, ss.t@test.com,s.m@test.com, ma.ram @test.com,mk.p@test.com

  Let us say when I select the table

  Select * from EMailNotify

  Id To_Address

  1a.b.ssl@test.com, ss.t@test.com,s.m@test.com, ma.ram @test.com,mk.p@test.com

  2a1.b.ssl@test.com, ss.t@test.com,s.m@test.com, ma.ram @test.com,mk.p@test.com

  3a2.b.ssl@test.com, ss.t@test.com,s.m@test.com, ma.ram @test.com,mk.p@test.com

  I want how to write a query which will remove all the ss.t@test.com mail id from the column “To_Address”

  After changing the query it should print like

  Select * from EMailNotify

  Id To_Address

  1a.b.ssl@test.com,s.m@test.com, ma.ram @test.com,mk.p@test.com

  2a1.b.ssl@test.com,s.m@test.com, ma.ram @test.com,mk.p@test.com

  3a2.b.ssl@test.com,s.m@test.com, ma.ram @test.com,mk.p@test.com

  Please help for the needful.

  Thanks a lot in advance.

  Regards,

  Sumanta Panda

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  November 11, 2009 at 11:29 pm

  #1078172

  Try this one!

  DECLARE @WORD VARCHAR(500)

  SET @WORD = 'ss.t@test.com, a.b.ssl@test.com,ss.t@test.com, s.m@test.com, ma.ram @test.com,mk.p@test.com,ss.t@test.com'

  SELECT REPLACE(REPLACE(@word,'ss.t@test.com,',''),',ss.t@test.com','')

  Inform us if it works!

  Cheers!

  [Edited to remove the white spaces in the REPLACE]