|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DISTINCT OR GROUP BYI have some records in my Sql Db.
There are some duplicate record in my email and adsoyad fields Email Adsoyad a@a.com Savas a@a.com Pele b@b.com Savas c@c.om Ilim d@d.com Hasan d@d.com Hasan I want to eliminate email address which can be duplicate and its Adsoyad field. There can be different adsoyad records for duplicate email records. I want to select one of them which doesnt have any importance for me . I also have soma email records which doesnt have @ character. I want to eliminate those records too. Is there any command in SQL like INSTR ? Do you want to delete the row or update the fields?
For deleting the rows with email without '@' Delete from table where email not like '_%@_%' I put the additional requirement that there must be at least one char in front of and 1 char behind the @ sign... The second thing you wanted to do is delete the dupe emails by picking the Adsoyad which does not have meaning for you.... You'll have to repst and describe how you determine which Adsoyad doesn't have meaning and someone will help you with the SQL... -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. "Savas Ates" wrote: > I have some records in my Sql Db. > There are some duplicate record in my email and adsoyad fields > > Email Adsoyad > a@a.com Savas > a@a.com Pele > b@b.com Savas > c@c.om Ilim > d@d.com Hasan > d@d.com Hasan > > I want to eliminate email address which can be duplicate and its Adsoyad > field. > There can be different adsoyad records for duplicate email records. I want > to select one of them which doesnt have any importance for me . > > I also have soma email records which doesnt have @ character. I want to > eliminate those records too. Is there any command in SQL like INSTR ? > > > > IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
one of adsoyad records ? "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com>, haber iletisinde þunlarý yazdý:E9100F46-9958-4408-B527-2209F25B4***@microsoft.com...Show quote > Do you want to delete the row or update the fields? > > For deleting the rows with email without '@' > > Delete from table where email not like '_%@_%' > > I put the additional requirement that there must be at least one char in > front of and 1 char behind the @ sign... > > The second thing you wanted to do is delete the dupe emails by picking the > Adsoyad which does not have meaning for you.... You'll have to repst and > describe how you determine which Adsoyad doesn't have meaning and someone > will help you with the SQL... > -- > Wayne Snyder MCDBA, SQL Server MVP > Mariner, Charlotte, NC > > I support the Professional Association for SQL Server ( PASS) and it''s > community of SQL Professionals. > > > "Savas Ates" wrote: > >> I have some records in my Sql Db. >> There are some duplicate record in my email and adsoyad fields >> >> Email Adsoyad >> a@a.com Savas >> a@a.com Pele >> b@b.com Savas >> c@c.om Ilim >> d@d.com Hasan >> d@d.com Hasan >> >> I want to eliminate email address which can be duplicate and its Adsoyad >> field. >> There can be different adsoyad records for duplicate email records. I >> want >> to select one of them which doesnt have any importance for me . >> >> I also have soma email records which doesnt have @ character. I want to >> eliminate those records too. Is there any command in SQL like INSTR ? >> >> >> >> > IT doesnt matter which adsoyad Record im gonna choose. I want to just pick Do you need adsoyad in the result? Can you provide more clear requirements > one of adsoyad records ? so we don't have to ask 80 follow-up questions? Please see http://www.aspfaq.com/5006 Yep I need adsoyad records too. But it doesnt matter which one i can come
up. I want to elimitate email addresses which are dublicate and adsoyad records whics is tied to one of the duplicate email addresses. I should say that i dont need to chooese any adsoyad records exactly . Just wanna pick up one of them . Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%2366CMQ1TGHA.1236@TK2MSFTNGP11.phx.gbl... >> IT doesnt matter which adsoyad Record im gonna choose. I want to just >> pick one of adsoyad records ? > > Do you need adsoyad in the result? Can you provide more clear > requirements so we don't have to ask 80 follow-up questions? Please see > http://www.aspfaq.com/5006 > > There can be different adsoyad records for duplicate email records. I want Use DISTINCT, then you can tell from your query that that is your purpose. > to select one of them which doesnt have any importance for me . GROUP BY is typically used for aggregation. > I also have soma email records which doesnt have @ character. I want to Yes, look at CHARINDEX, PATINDEX. You might also consider a function or > eliminate those records too. Is there any command in SQL like INSTR ? even a check constraint that actually validates the format of an e-mail address. Then you can't get any crap in there in the first place. Search groups.google.com, there are plenty of examples out there ready to use. (reposting using the Microsoft's site, since the Google Groups post was not
found here) Hi, Savas Try something like this (untested): SELECT Email, MIN(Adsoyad) FROM YourTable WHERE Email LIKE '_%@_%._%' AND Email NOT LIKE '%@%@%' GROUP BY Email Note that the LIKE expression above (although more complex than what you have suggested), still doesn't ensure a valid e-mail address (as per RFC 822). Razvan  |
|||||||||||||||||||||||
Other interesting topics