emailid checking problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

emailid checking problem

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-08-21 : 09:03:18

Hi all,
ihave table with field called email.this field can assume comma separated email values. email datatype is nvarchar.

Before entering an email data into this field, i have to check whether
input email data contains an emailid that is already entered into this column in any set.

how to do this checking?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 09:09:13

quote:
Originally posted by soorajtnpki

Hi all,
ihave table with field called email.this field can assume comma separated email values. email datatype is nvarchar.

Before entering an email data into this field, i have to check whether
input email data contains an emailid that is already entered into this column in any set.

how to do this checking?

Why are you storing multiple email ids in a single column?
Read about normalization

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-08-21 : 09:14:52

ok madhivanan,
wat u said is right,
But now i need a solution ,can u help me , its urgent...

thanks in advance..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 09:19:58

if exists(select * from table where ','+email+',' like '%,'+@email+',%')
--insert

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-08-21 : 09:27:11

thanks
let me try

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-08-21 : 09:37:38

its not working..
when we enter an existing email in db with a new email not in db, it is entering into db..

ok tanx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 09:41:56

quote:
Originally posted by soorajtnpki

its not working..
when we enter an existing email in db with a new email not in db, it is entering into db..

ok tanx

use if not exists

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-08-21 : 09:43:08

ys
i used that
but its not working for above case..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 09:44:26

What do you want to do if an email already exists?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 09:47:48

quote:
Originally posted by soorajtnpki

Hi all,
ihave table with field called email.this field can assume comma separated email values. email datatype is nvarchar.

Before entering an email data into this field, i have to check whether
input email data contains an emailid that is already entered into this column in any set.

how to do this checking?

will you be passing input email value also as comma seperated values? or will it be a single value which will be compared with csvs on email field in table?

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-08-21 : 09:48:50

hi
yes, if u r inputting a set of comma separarted emailid's, and then if any one of tat emailid is already present in the db, i would not allow to input . thats it..

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-08-21 : 09:57:32

hi visakh,

ys i am passing email value also as comma seperated values into db.
so wat i need is check the input(a set of emailid's) with the column .
each column consists of comma separated emailidvalues.

thanx in advance

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-08-21 : 10:22:55

hi all its urgent..
tanx in advance

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-21 : 12:15:23

Maybe this will help?

DECLARE @Yak TABLE (ID INT, EmailList NVARCHAR(2000))
INSERT @Yak
SELECT 1, 'foo@foo.com'
UNION ALL SELECT 2, 'bar.bar,com,do@do.com'
UNION ALL SELECT 3, 'goo@goo.com,soo@soo.com'

DECLARE @Email AS NVARCHAR(255)
--SET @Email = 'foo@foo.com'
SET @Email = 'hoo@hoo.com'

IF EXISTS (SELECT * FROM @Yak WHERE EmailList like '%' + @email + '%')
BEGIN
	PRINT 'Exists'
END
ELSE
BEGIN
	PRINT 'Does Not Exist'
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 12:57:41

create the below function:-

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

then use like this

IF NOT EXISTS (SELECT 1 FROM YourTable t INNER JOIN dbo.ParseValues(@EmailList) b ON  ','+ t.Email +',' LIKE '%,'+ b.Val + ',%')
--insert code here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources