Replacing Partial String Data in Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Replacing Partial String Data in Column

Author

Topic

rbtroj
Starting Member

2 Posts

Posted - 2009-02-11 : 12:19:49

I've got a table with an "Email" column that contains multiple different email addresses (semicolon-separated) in each record. So, for instance, in Row 1 the email column may contain 'abc@def.com; ghi@jkl.com;' and Row 2 might have 'mno@pqr.com; stu@vwx.com; abc@def.com;'.

My question is, can I use SQL Replace in some way to alter ONLY the 'abc@def.com' string even if it doesn't occur in the same position in each row? Or is this an exercise in madness?

Thanks in advance for any help.

- Rob

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-02-11 : 12:33:55

I think so.. have you tried the REPLACE function?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rbtroj
Starting Member

2 Posts

Posted - 2009-02-11 : 12:41:15

quote:
Originally posted by dinakar

I think so.. have you tried the REPLACE function?

Yes, but I'm not familiar with it so I'm sure I'm not doing it correctly. Since the string is different in every record, I'm not sure how to tell it what to search.

So, the following does not work for me (obviously):

REPLACE ('abc@def.com', 'abc@def.com', 'fed@cba.com') as Expr1 where id= 101

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-11 : 13:58:41

I think based on your requirement, this will do.
declare @t table (Pol_Num varchar(10), EMail varchar(100))
insert @t
select 'F1111111', 'a@b.com;b@c.com;c@d.com' union all
select 'F1111112', 'b@c.com;a@b.com;c@d.com' union all
select 'F1111113', 'a@b.com;b@c.com;c@d.com'

update @t
set EMail = REPLACE(EMail,'a@b.com;','')

select * from @t

Output
------
F1111111 b@c.com;c@d.com
F1111112 b@c.com;c@d.com
F1111113 b@c.com;c@d.com

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-11 : 23:05:05

UPDATE Urtable
SET email = REPLACE(email,'abc@def.com;','replacementstring')

Jai Krishna

jbp_j
Starting Member

24 Posts

Posted - 2009-02-12 : 07:13:04

Hi,

I thinks this is solve u r problem.

if u r email column have 'abc@def.com;' this String
Then it replace in First Position.

declare @t table (Pol_Num varchar(10), EMail varchar(100))
insert @t
select 'F1111111', 'abc@def.com; ghi@jkl.com;' union all
select 'F1111113', 'mno@pqr.com; stu@vwx.com; abc@def.com;'union all
select 'F1111113', 'stu@vwx.com; abc@def.com;'

update @t
set email = case when charindex('abc@def.com;',Email) <> 0
then stuff(replace(email,'abc@def.com;',''),1,0,'abc@def.com;')
else email end

update @t
set EMail = REPLACE(EMail,'a@b.com;','')

select * from @t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources