August 15, 2014 at 3:20 am
Hello,
I have one table about 1000 rows.
My table is from these columns - address, alias.
Several data from my table:
Nraddress alias
2X2@gmail.comX2@exc.gmail.ex,X3@exc.bcm.ex
3X4@gmail.comX4@exc.gmail.ex,X5@exc.bbx.ex,X6@exc.bbx.ex
4X7@co.com X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex
6X12@nor.comX12@exc.nor.ex,X13@exc.nor.ex
I would like get this result:
Nr address alias
1
3X4@gmail.comX5@exc.bbx.ex,X6@exc.bbx.ex
4X7@co.com X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex
5
I have attached excel file with data.
Can somebody help me for it?
Thank you very much.
August 15, 2014 at 5:16 am
How do you get from your data to your result? I see things not reported but why?
August 15, 2014 at 12:01 pm
I'm not even seeing the logic between the 3 columns of the original data and the rows that appear to have more than three values.
Please post your table DDL and an INSERT statement for your data so we can see how the data fits in these columns. Then post an english language version (as opposed to a code version) of what you are trying to achieve with the results.
August 17, 2014 at 11:13 am
Hello,
I have added to declare table:
declare @info table ( nr int, [address] varchar(100), alias varchar(100))
INSERT INTO @info (nr, [address],alias)
Select 1,'x1@yahoo.com','X1@exc.yahoo.ex'
union all
Select 2,'X2@gmail.com','X2@exc.gmail.ex,X3@exc.bcm.ex'
union all
Select 3,'X4@gmail.com','X4@exc.gmail.ex,X5@exc.bbx.ex,X6@exc.bbx.ex'
union all
Select 4, 'X7@co.com', 'X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex'
union all
Select 5,'X11@x.com', 'X11@exc.x.ex'
union all
Select 6,'X12@nor.com','X12@exc.nor.ex,X13@exc.nor.ex'
I would like get this result:
declare @result table ( nr int, [address] varchar(100), alias varchar(100))
INSERT INTO @result (nr, [address],alias)
Select 1,'', ''
union all
Select 2,'X2@gmail.com','X3@exc.bcm.ex'
union all
Select 3,'X4@gmail.com','X5@exc.bbx.ex,X6@exc.bbx.ex'
union all
Select 4,'X7@co.com','X8@exc.gmail.ex,X2@exc.yahoo.ex,X10@exc.coo.ex'
union all
Select 5,'',''
union all
Select 6,'X12@nor.com','X13@exc.nor.ex'
Select * from @result
What should I use a query for this result?
August 17, 2014 at 12:38 pm
You seem to be looking for cases with more than 1 address alias, which can be detected by having a comma (",") in the alias column.
So maybe this will work:
SELECT nr
, case when alias not like '%,%' then NULL else [address] end
, case when alias not like '%,%' then NULL else alias end
from @info
That's not very efficient, but your info table isn't even in 1st normal form and efficiency is not going to happen when you start off with a table which is disastrously deficient in normalisation.
Tom
August 17, 2014 at 4:44 pm
Thank you for the simple query.
Yes,the table alias column has more than 1 value.
I would like to compare address column with alias column.
If address column is equal alias column then alias column is as NULL if not than it leave the current alias value.
August 18, 2014 at 4:22 am
So did Tom answer your question, then?
If so, please mark his post as the solution. If not, please post again and tell us why it didn't work for you.
August 18, 2014 at 4:58 pm
Brandie Tarvin (8/18/2014)
So did Tom answer your question, then?If so, please mark his post as the solution. If not, please post again and tell us why it didn't work for you.
No, my post doesn't answer it completely - it doesn't handle the com to ex match, and it doesn't handle removing a match from a multi-entry alias, and it doesn't handle a single entry alias which doesn't match (with ex for com) the address.
I don't have time just now, maybe if no-one else suggests anything before late wednesday (when I may have some spare time) I'll do something.
But maybe Myke85 can do it: needs to look up "replace" and use it several times in the query (for getting a string with ex from a string with com to see if there's a match, and then for replacing a string that matches and its delimiting comma by an empty string in an alias with more than one address in it).
Tom
August 19, 2014 at 4:17 am
My problem is that he still hasn't explained in English what he expects the result to be. I told him specifically NOT to explain it in code and that's what he did... Write code.
So without understanding his logic behind his expected solution, I'm not even going to try to figure out the answer. The coded result just doesn't make sense to me given the data he provided.
August 21, 2014 at 12:34 pm
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
August 21, 2014 at 12:43 pm
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
You can't. The LIKE comparison doesn't work that way. PATINDEX and CHARINDEX are a better choice in this case.
I'll see what I can work up as a solution to this issue, but it won't be until tomorrow morning earliest. In the meantime, if someone else has a solution, I'm sure they will post.
August 22, 2014 at 4:00 am
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
August 22, 2014 at 4:22 am
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.
August 22, 2014 at 4:27 am
Ok, i missed the variable name "@email".
I'm Sorry and, no, i didn't test it
August 22, 2014 at 4:27 am
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. 🙂
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply