Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Selecting Domain name from Email address

Jun 20 2011 2:24AM by Madhivanan   

Pinal Dave, in his site, posted a blog post about selecting domain name from the email address. He has used right function. Here is another method that will use STUFF function and does the same job.

declare @t table(email varchar(100))
insert into @t 
select 'test@yahoo.com' union all
select 'test@msn.com' union all
select 'test@gmail.com' union all
select 'test1@yahoo.com' union all
select 'test@sify.com' union all
select 'test2@yahoo.com' union all
select 'test3@msn.com' union all
select '' union all
select null union all
select 'test1@gmail.com' 

The Query is

select 
	stuff(email,1,charindex('@',email),'') as domain,
	count(*) as emailcount 
from 
	@t
where  
	email > ''
group by 
	stuff(email,1,charindex('@',email),'')
order by 
	emailcount desc

The output is

domain                                  emailcount
-------------------------              -----------
yahoo.com                                 3
gmail.com                                 2
msn.com                                   2
sify.com                                  1

Tags: t-sql, sql_server, parsing, sqlserver, tsql, BRH, stuff, email,


Madhivanan
3 · 40% · 12968
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

6  Comments  

  • Nice, I did not know this. Cool stuff Madhi Sr.

    commented on Jun 20 2011 7:22AM
    Pinal Dave
    151 · 1% · 326
  • Very interesting technique madhivanan. Here is an XML version.

    declare @t table(email varchar(100))
    insert into @t 
    select 'test@yahoo.com' union all
    select 'test@msn.com' union all
    select 'test@gmail.com' union all
    select 'test1@yahoo.com' union all
    select 'test@sify.com' union all
    select 'test2@yahoo.com' union all
    select 'test3@msn.com' union all
    select '' union all
    select null union all
    select 'test1@gmail.com' 
    
    ;WITH cte AS (
        SELECT CAST(
        	'<i>' + REPLACE(email, '@', '</i><i>') + '</i>'
        	AS XML).value('/i[2]', 'varchar(50)') AS Domain
        FROM @t 
    )
    SELECT
        Domain, 
        COUNT(*) AS Cnt
    FROM cte
    WHERE Domain IS NOT NULL
    GROUP BY Domain 
    ORDER BY COUNT(*) DESC
    
    /*
    Domain                                             Cnt
    -------------------------------------------------- -----------
    yahoo.com                                          3
    gmail.com                                          2
    msn.com                                            2
    sify.com                                           1
    */
    
    commented on Jun 20 2011 8:03AM
    Jacob Sebastian
    1 · 100% · 32235
  • Another method is

    select 
        substring(email,charindex('@',email)+1,len(email)) as domain,
        count(*) as emailcount 
    from 
        @t
    where
        email>''
    group by
        substring(email,charindex('@',email)+1,len(email))
    order by
        emailcount desc
    
    commented on Jun 20 2011 8:12AM
    Madhivanan
    3 · 40% · 12968
  • Hi.. How stuff function works? Can u please explain?

    commented on Jun 21 2011 1:12AM
    Rajkumar
    282 · 0% · 153
  • Man, this is worth a big blog post! Loved the subject.

    commented on Jun 21 2011 1:22AM
    Pinal Dave
    151 · 1% · 326
  • rajbluesea, Stuff function is used to replace certains characters to other characters. In this case it finds the position of the character @, then replaces all characters from first character to @ by empty string. So you will have only domain name

    commented on Jun 28 2011 3:38AM
    Madhivanan
    3 · 40% · 12968

Your Comment


Sign Up or Login to post a comment.

"Selecting Domain name from Email address" rated 5 out of 5 by 2 readers
Selecting Domain name from Email address , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]