August 22, 2014 at 4:30 am
Myke85 (8/21/2014)
How I can get 'good'?
DECLARE @Email VARCHAR(100)
DECLARE @Email2 VARCHAR(100)
Set @email= 'helper@sql-server-helper.com'
set @email2='fff@dd.com, helper@sql-server-helper.com,vc@.com'
SELECT case when @email like @email2 then 'good' else 'false' end as Result
Okay, the first problem is, as lpablo pointed out, you're trying to do the LIKE in the wrong order. You should be seeing if Email2 is like Email, not if Email is like Email2.
The second issue is that while lpablo has the right idea, he forgot to test his code. Because if you look at it, he's referencing a non-existent variable in the SELECT statement. So here are two versions of your problem, one with variables (correcting lpablo's issue) and one with a table:
DECLARE @Email VARCHAR(100), @Email2 VARCHAR(100);
SET @email= 'helper@sql-server-helper.com';
SET @email2='fff@dd.com, helper@sql-server-helper.com,vc@.com';
SELECT CASE WHEN @email2 LIKE '%' + @email + '%' THEN 'good' ELSE 'false'
END AS Result ;
--This is the variable code.
CREATE TABLE #Emails (Email1 VARCHAR(100), Email2 VARCHAR(100));
INSERT INTO #Emails (Email1, Email2)
VALUES ('helper@sql-server-helper.com',
'fff@dd.com, helper@sql-server-helper.com,vc@.com');
SELECT * FROM #Emails;
SELECT CASE WHEN Email2 LIKE '%' + Email1 + '%' THEN 'Good' ELSE 'False'
END AS Result
FROM #Emails;
--This is the table version.
FYI: If you use "code" instead of "quote" around your T-SQL, it will appear as it does above (in full technicolor glory).
August 22, 2014 at 4:37 am
Aaaannnnnddddd... From the theatre of the absurd, I couldn't get PATINDEX() to work, but I did get CHARINDEX() and REPLACE() to work. I don't know why anyone would want to use these methods, but hey, I'm gonna post them anyway. @=)
--The table version
CREATE TABLE #Emails (Email1 VARCHAR(100), Email2 VARCHAR(100));
INSERT INTO #Emails (Email1, Email2)
VALUES ('helper@sql-server-helper.com',
'fff@dd.com, helper@sql-server-helper.com,vc@.com');
SELECT * FROM #Emails;
SELECT CASE WHEN Email2 LIKE '%' + Email1 + '%' THEN 'Good' ELSE 'False'
END AS Result
FROM #Emails;
SELECT CASE WHEN REPLACE(Email2,Email1,'Good') LIKE '%Good%' THEN 'Good'
ELSE 'False' END AS Result
FROM #Emails;
--Using REPLACE
SELECT CASE WHEN CHARINDEX(Email1,Email2) <> 0 THEN 'Good'
ELSE 'False' END AS Result
FROM #Emails;
--Using CHARINDEX()
August 22, 2014 at 4:46 am
djj (8/22/2014)
Brandie Tarvin (8/22/2014)
lpablo (8/22/2014)
DECLARE @Email VARCHAR(100)DECLARE @Email2 VARCHAR(100)
Set @email= 'helper@sql-server-helper.com'
set @email2='fff@dd.com, helper@sql-server-helper.com,vc@.com'
SELECT case when @email2 like '%' + @email1 + '%' then 'good' else 'false' end as Result
FYI: This code has obviously not been tested.
But if you replace @email1 with @email it will work. 🙂
Why, Yes. Yes, it will. @=)
August 22, 2014 at 6:07 am
WITH DedupedData AS (
SELECT nr, [Address],
Alias = CASE
WHEN (x1.Address_Name = x3.Alias_Name AND x1.Address_Domain IN (Alias_Domain1,Alias_Domain2)) THEN NULL
ELSE Item END
FROM @info
CROSS APPLY (
SELECT
Address_Name = MAX(CASE WHEN ItemNumber = 1 THEN Item END),
Address_Domain = MAX(CASE WHEN ItemNumber = 2 THEN Item END)
FROM dbo.DelimitedSplit8k(REPLACE([address],'@','.'),'.')
WHERE ItemNumber IN (1,2)
) x1
CROSS APPLY dbo.DelimitedSplit8k([alias],',') x2
CROSS APPLY (
SELECT
Alias_Name = MAX(CASE WHEN ItemNumber = 1 THEN Item END),
Alias_Domain1 = MAX(CASE WHEN ItemNumber = 2 THEN Item END),
Alias_Domain2 = MAX(CASE WHEN ItemNumber = 3 THEN Item END)
FROM dbo.DelimitedSplit8k(REPLACE(x2.Item,'@','.'),'.')
) x3
)
SELECT
d.nr,
[address] = CASE WHEN x.Alias IS NOT NULL THEN d.[address] ELSE '' END,
[alias] = ISNULL(x.alias,'')
FROM (SELECT nr, [address] FROM DedupedData GROUP BY nr, [address]) d
OUTER APPLY (
SELECT stuff( (
SELECT ',' + Alias
FROM DedupedData di
WHERE di.nr = d.nr
AND di.Alias IS NOT NULL
ORDER BY Alias
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
,1,1,'')
) x (alias)
ORDER BY d.nr
For definition of function DelimitedSplit8k, see http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2014 at 6:14 am
Chris,
Why the STUFF() and the XML?
August 22, 2014 at 6:18 am
Brandie Tarvin (8/22/2014)
Chris,Why the STUFF() and the XML?
It's concatenation of values from different rows of the same column, Brandie.
Split up the email addresses onto different rows, eliminate any matches, then join back up what's left.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2014 at 6:24 am
ChrisM@Work (8/22/2014)
Brandie Tarvin (8/22/2014)
Chris,Why the STUFF() and the XML?
It's concatenation of values from different rows of the same column, Brandie.
Split up the email addresses onto different rows, eliminate any matches, then join back up what's left.
Ah. Thank you.
I wish I had the OPs DDL so I could test stuff like this. Oh, well.
August 22, 2014 at 6:37 am
I'll post it up when I get back from lunch 🙂
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 22, 2014 at 6:47 am
CASE WHEN PATINDEX(REPLACE(LEFT(address,CHARINDEX('.',address)-1),'@','@exc.')+'%,',alias+',') > 0
THEN STUFF(alias,PATINDEX(REPLACE(LEFT(address,CHARINDEX('.',address)-1),'@','@exc.')+'%,',alias+','),
(CHARINDEX(',',alias+',',PATINDEX(REPLACE(LEFT(address,CHARINDEX('.',address)-1),'@','@exc.')+'%,',alias+','))
- PATINDEX(REPLACE(LEFT(address,CHARINDEX('.',address)-1),'@','@exc.')+'%,',alias+','))+1,'')
ELSE alias
END
Far away is close at hand in the images of elsewhere.
Anon.
August 22, 2014 at 6:58 am
Brandie Tarvin (8/22/2014)
ChrisM@Work (8/22/2014)
Brandie Tarvin (8/22/2014)
Chris,Why the STUFF() and the XML?
It's concatenation of values from different rows of the same column, Brandie.
Split up the email addresses onto different rows, eliminate any matches, then join back up what's left.
Ah. Thank you.
I wish I had the OPs DDL so I could test stuff like this. Oh, well.
There's a ton of stuff on t'webs about FOR XML PATH, the Simple Talk article is pretty good.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 22, 2014 at 7:41 am
ChrisM@Work (8/22/2014)
Brandie Tarvin (8/22/2014)
ChrisM@Work (8/22/2014)
Brandie Tarvin (8/22/2014)
Chris,Why the STUFF() and the XML?
It's concatenation of values from different rows of the same column, Brandie.
Split up the email addresses onto different rows, eliminate any matches, then join back up what's left.
Ah. Thank you.
I wish I had the OPs DDL so I could test stuff like this. Oh, well.
There's a ton of stuff on t'webs about FOR XML PATH, the Simple Talk article is pretty good.
DOH. I got my threads mixed up. I thought this was the one where I was still waiting for OP DDL. It's not. Sorry.
August 22, 2014 at 11:03 am
Brandie Tarvin (8/22/2014)
djj (8/22/2014)
Brandie Tarvin (8/22/2014)
lpablo (8/22/2014)
DECLARE @Email VARCHAR(100)DECLARE @Email2 VARCHAR(100)
Set @email= 'helper@sql-server-helper.com'
set @email2='fff@dd.com, helper@sql-server-helper.com,vc@.com'
SELECT case when @email2 like '%' + @email1 + '%' then 'good' else 'false' end as Result
FYI: This code has obviously not been tested.
But if you replace @email1 with @email it will work. 🙂
Why, Yes. Yes, it will. @=)
Actually it gives the right answer for this case. But if we had slightly different data is would give the wrong answer:
DECLARE @Email VARCHAR(100)
DECLARE @Email2 VARCHAR(100)
Set @email= 'helper@sql-server-helper.com'
set @email2='fff@dd.com, badhelper@sql-server-helper.com,vc@.com'
SELECT case when @email2 like '%' + @email1 + '%' then 'good' else 'false' end as Result
Using exactly the same case statement, it gives the answer "good" although it should give "false" since the address in @email is not in the list of addresses in @email2.
Using a splitter to get the strings between commas in @email2, then ltrim and rtrim to eliminate leading and trailing spaces on each of those strings, and finally testing @email (with leading and trailing spaces removed) to test whether it is equal to any of the trimmed strings derived from @email1, will work. That's quite a size and complexity code penalty for failure to normalise.
Tom
August 22, 2014 at 11:07 am
That is a good point, Tom. And not something even my Theatre of the Absurd code addresses.
August 23, 2014 at 1:52 pm
I have written finally the query.
create function testtable (@address varchar(200))
returns @table table (alias varchar (200))
as begin
declare @alias varchar (200)
declare @value varchar (200)
declare @email varchar (200)
--set @alias='fff@dd.com, helper@sql-server-helper.com,vc@.com'
set @alias=@address
while len(@alias)>0
begin
set @value= substring(@alias,1,isnull(nullif(charindex(',',@alias)-1,-1),len(@alias)))
set @alias = substring(@alias, isnull(nullif(charindex(',',@alias),0),len(@alias))+1,len(@alias))
insert into @table (alias) values (@value)
end
return
end
DECLARE @Emailas VARCHAR(200)
DECLARE @Emailas2 VARCHAR(200)
declare @table2 table (email varchar(200),email2 varchar(200))
Set @emailas= 'helper@sql-server-helper.com'
set @emailas2='fff@dd.com, helper@sql-server-helper.com,vc@.com'
insert into @table2 (email, email2) values(@emailas, @emailas2)
Select *, case when email like '%'+alias+'%' and len(email)=len(alias) then 'Good' else 'No' end as Status from (
Select * from @table2 as tb
cross apply
testtable (replace(tb.email2,' ','')) ) as fin
August 25, 2014 at 5:56 am
Myke85, your code still has the same problem that Tom pointed out. Are you sure that's the code you want to stick with?
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply