how to update - SQL Server Forums

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

how to update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-02-27 : 17:51:09

I have a data like this:
id to
1 <aa@abc.com;cc@xyz.com;mno@abc.com>
2 xyz;<mm@mno.com;sss@sdd.com>

i have to updates RecipentDomain: only domain name (if there are multiple email address but same domain name just update one. And sendername: updater sender name if there is no domain name.

WITH From_Email AS (
SELECT ID,
CAST('<i>' + REPLACE(REPLACE(REPLACE(CAST([to] as NVARCHAR(4000))+','+COALESCE(CAST([cc] AS nvarchar(4000)),'')+','+COALESCE(CAST([bcc] AS nvarchar(4000)),''),'<',''),'>',''), ';', '</i><i>') + '</i>' AS XML) AS EmailID,
RecipentDomain
FROM emails WHERE cast([to] AS NVARCHAR(4000)) LIKE '%@%'
)
,MailDomain AS
(
SELECT ID,
SUBSTRING(x.i.value('.', 'VARCHAR(1000)'),CHARINDEX('@',x.i.value('.', 'VARCHAR(1000)'))+1, LEN(x.i.value('.', 'VARCHAR(1000)'))) AS EID,
RecipentDomain
FROM From_Email
CROSS APPLY EmailID.nodes('/i') x(i)
)
UPDATE MailDomain
SET RecipentDomain=
CASE WHEN CHARINDEX('@',EId) >0 THEN
LEFT(EId,CHARINDEX('@',EId)-1)
ELSE EID
END
WHERE EID=EID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 02:48:37

Do you have any problem with the code?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 08:58:00

does maindomain have multiple columns for an id value of email?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-03 : 09:58:49

hi visakh16,
no, it has single column but it seperated by , or ;
e.g.
kim;aa@abc.com;xyz@aa.com;mno@abc.com

above e.g. there are two abc.com, one aa.com and one just name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 10:03:55

so in that case what should be domain name you update it with?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-03 : 10:30:09

if i have recrod like this:
kim;aa@abc.com;xyz@aa.com;mno@abc.com

i need to update MailDomain
output will be: abc.com;aa.com

and update on nodomain :
kim

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-03 : 11:57:08

visakh16, did u get the solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 12:26:57

do you mean you want to update two tables? then where does your data come from?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-03 : 12:39:21

yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 12:41:39

learn how to post a question to get quick and accurate solutions

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-03 : 23:58:17

How do i update MailDomain and MailName (on MailDomain, update domain names from mails, on mailname, update emails name if there is no domain name)

create table #tbl1
(ID INT,
Emails varchar(255),
MailDomain varchar(255),
EName varchar(255)
)

INSERT INTO #tbl1
SELECT 1, 'abc@xyz.com;mnop@ab.com;kkk@xyz.com',NULL,NULL

INSERT INTO #tbl1
SELECT 2, 'kim;<aa@ab.com;dd@xyz.com>',NULL,NULL

INSERT INTO #tbl1
SELECT 3, aa;b;cc;dd;<ss@kk.com;dkd@kk.com;sss@kk.com>',NULL,NULL

INSERT INTO #tbl1
SELECT 4, '<dd@mn.com;ss@ss.com;kd@dd.net;sss@kk.net>',NULL,NULL
SELECT * FROM #tbl1

The output will be:
id DomainName EName
1 xyz.com;ab.com NULL
2 ab.com;xyz.com Kim
3 kk.com aa;b;cc;dd
4 mn.com;ss.com;dd.netkk.net NULL

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-04 : 01:04:47

try this it will be useful for limited records only


declare @tbl1 table 
(ID INT,
Email varchar(255),
MailDomain varchar(255),
EName varchar(255)
)

INSERT INTO @tbl1
SELECT 1, 'abc@xyz.com;mnop@ab.com;kkk@xyz.com',NULL,NULL

INSERT INTO @tbl1
SELECT 2, 'kim;<aa@ab.com;dd@xyz.com>',NULL,NULL

INSERT INTO @tbl1
SELECT 3, 'aa;b;cc;dd;<ss@kk.com;dkd@kk.com;sss@kk.com>',NULL,NULL

INSERT INTO @tbl1
SELECT 4, '<dd@mn.com;ss@ss.com;kd@dd.net;sss@kk.net>',NULL,NULL

SELECT	id,	
		SUBSTRING(s.Email,charindex(';',s.Email,v.number)+1,abs(charindex(';',s.Email,charindex(';',s.Email,v.number)+1)-1-charindex(';',s.Email,v.number)))as email,
		evalue
into #temp
FROM		
(SELECT id,case when charindex('<',email,1) > 0 then substring(email,1,charindex('<',email,1)-1) else null end as evalue,
case when charindex('<',email,1) > 0 then replace(substring(email,charindex('<',email,1)+1,len(email)),'>','') else email end as email FROM @tbl1
) AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
			and v.number > 0
			and v.number <= len(s.Email)
WHERE		substring(';' + s.Email, v.number, 1) = ';'

select distinct id, stuff((select distinct ','+
 substring(email, charindex('@',email,1)+1,len(email)) from #temp where id = t.id for xml path('')),1,1,'')as domainname,evalue as ename
from #temp t

drop table #temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 08:49:49

quote:
Originally posted by rudba

How do i update MailDomain and MailName (on MailDomain, update domain names from mails, on mailname, update emails name if there is no domain name)

create table #tbl1
(ID INT,
Emails varchar(255),
MailDomain varchar(255),
EName varchar(255)
)

INSERT INTO #tbl1
SELECT 1, 'abc@xyz.com;mnop@ab.com;kkk@xyz.com',NULL,NULL

INSERT INTO #tbl1
SELECT 2, 'kim;<aa@ab.com;dd@xyz.com>',NULL,NULL

INSERT INTO #tbl1
SELECT 3, aa;b;cc;dd;<ss@kk.com;dkd@kk.com;sss@kk.com>',NULL,NULL

INSERT INTO #tbl1
SELECT 4, '<dd@mn.com;ss@ss.com;kd@dd.net;sss@kk.net>',NULL,NULL
SELECT * FROM #tbl1

The output will be:
id DomainName EName
1 xyz.com;ab.com NULL
2 ab.com;xyz.com Kim
3 kk.com aa;b;cc;dd
4 mn.com;ss.com;dd.netkk.net NULL

why do you want to store it as delimited values in table? read and understand about normalisation.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-04 : 09:39:17

The output will be:
id DomainName EName
1 xyz.com,ab.com NULL
2 ab.com,xyz.com Kim
3 kk.com aa;b;cc;dd
4 mn.com,ss.com,dd.netkk.net NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 09:47:51

are you using sql 2005?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-04 : 10:28:10

yes, SQL Server 2005 with SP2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 10:34:45

[code]
SELECT ID,f.Val INTO #Temp
FROM Table t
CROSS APPLY dbo.ParseValues(t.to,';')f

SELECT DISTINCT ID,STUFF((SELECT ','+ Val FROM #Temp WHERE ID=t.ID AND CHARINDEX('@',Val)=0 FOR XML PATH('')),1,1,'') AS DomainName,
STUFF((SELECT ','+ Val FROM #Temp WHERE ID=t.ID AND CHARINDEX('@',Val)=0 FOR XML PATH('')),1,1,'') AS EName
FROM #Temp

DROP TABLE #Temp
[/code]
ParseValues function can be found below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113563

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-04 : 11:19:05

i got the err:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.

use mydb
go
SELECT ID,f.Val INTO #Temp
FROM tbl1 t
CROSS APPLY dbo.ParseValues(t.to,';')f

SELECT DISTINCT ID,STUFF((SELECT ','+ Val FROM #Temp WHERE ID=t.ID AND CHARINDEX('@',Val)=0 FOR XML PATH('')),1,1,'') AS DomainName,
STUFF((SELECT ','+ Val FROM #Temp WHERE ID=t.ID AND CHARINDEX('@',Val)=0 FOR XML PATH('')),1,1,'') AS EName
FROM #Temp

DROP TABLE #Temp

Note: i created ParseValues function from above link.
I have field name [to] on tbl1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 11:25:24

are you doing this in sql management studio query editor? also query should be below


SELECT ID,f.Val INTO #Temp
FROM Table t
CROSS APPLY dbo.ParseValues(t.to,';')f

SELECT DISTINCT ID,STUFF((SELECT DISTINCT ','+ SUBSTRING(Val,CHARINDEX('@',Val)+1,LEN(Val)) FROM #Temp WHERE ID=t.ID AND CHARINDEX('@',Val)>0 FOR XML PATH('')),1,1,'') AS DomainName,
STUFF((SELECT DISTINCT ','+ Val FROM #Temp WHERE ID=t.ID AND CHARINDEX('@',Val)=0 FOR XML PATH('')),1,1,'') AS EName 
FROM #Temp

DROP TABLE #Temp

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-04 : 11:27:59

yes i am, i cast(t.to as nvarchar(255))='' also because on the table [to] data type is ntext

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-04 : 12:11:04

bklr, i tried your script, but i lost....i have a field name [to] and also try to update thoes values on MailDomain and EName fields.

quote:

Originally posted by bklr

try this it will be useful for limited records only


declare @tbl1 table 
(ID INT,
Email varchar(255),
MailDomain varchar(255),
EName varchar(255)
)

INSERT INTO @tbl1
SELECT 1, 'abc@xyz.com;mnop@ab.com;kkk@xyz.com',NULL,NULL

INSERT INTO @tbl1
SELECT 2, 'kim;<aa@ab.com;dd@xyz.com>',NULL,NULL

INSERT INTO @tbl1
SELECT 3, 'aa;b;cc;dd;<ss@kk.com;dkd@kk.com;sss@kk.com>',NULL,NULL

INSERT INTO @tbl1
SELECT 4, '<dd@mn.com;ss@ss.com;kd@dd.net;sss@kk.net>',NULL,NULL

SELECT	id,	
		SUBSTRING(s.Email,charindex(';',s.Email,v.number)+1,abs(charindex(';',s.Email,charindex(';',s.Email,v.number)+1)-1-charindex(';',s.Email,v.number)))as email,
		evalue
into #temp
FROM		
(SELECT id,case when charindex('<',email,1) > 0 then substring(email,1,charindex('<',email,1)-1) else null end as evalue,
case when charindex('<',email,1) > 0 then replace(substring(email,charindex('<',email,1)+1,len(email)),'>','') else email end as email FROM @tbl1
) AS s
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
			and v.number > 0
			and v.number <= len(s.Email)
WHERE		substring(';' + s.Email, v.number, 1) = ';'

select distinct id, stuff((select distinct ','+
 substring(email, charindex('@',email,1)+1,len(email)) from #temp where id = t.id for xml path('')),1,1,'')as domainname,evalue as ename
from #temp t

drop table #temp

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources