Share Knowledge...
Knowledge is happiness...
Monday, August 27, 2012
Thursday, August 9, 2012
Monday, February 13, 2012
SQL SERVER – Selecting Domain from Email Address
Following script which will extract the domain and will also count how many email addresses are there with the same domain address.
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 'test4' union all
select '' union all
select null union all
select 'test1@gmail.com'
Method #1
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
Method #2
SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROM @t
WHERE LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC
SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROM @t
WHERE LEN(Email) > 0 and CHARINDEX('@', email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC
Method #3
;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
Method #4
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
Above script will select the domain after @ character. Please note, if there is more than one @ character in the email, this script will not work as that email address is already invalid.
domain emailcount
yahoo.com 3
gmail.com 2
msn.com 2
sify.com 1
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 'test4' union all
select '' union all
select null union all
select 'test1@gmail.com'
Method #1
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
Method #2
SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROM @t
WHERE LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC
SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROM @t
WHERE LEN(Email) > 0 and CHARINDEX('@', email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC
Method #3
;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
Method #4
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
Above script will select the domain after @ character. Please note, if there is more than one @ character in the email, this script will not work as that email address is already invalid.
domain emailcount
yahoo.com 3
gmail.com 2
msn.com 2
sify.com 1
Different ways to know structure of a table
Generate SQL Script option from Enterprise Manager/Management Studio
select * from information_schema.columns where table_name='table_name'
EXEC sp_help 'table_name'
EXEC sp_columns 'table_name'
In Query Analyser type the name of the table, highlight it and press Alt+F1
Monday, August 1, 2011
Dynamically change the proper case
create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);
select @Reset = 1, @i=1, @Ret = '';
while (@i <= len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i +1
return @Ret
end
select dbo.ProperCase('this,my friends, is a test.wHat DO you think?i like shaquile o''neal')
Dynamic Change the text case in SQL SERVER
CREATE FUNCTION udf_TitleCase (@x varchar(7999))
RETURNS varchar(7999)
AS
BEGIN
DECLARE @y int
SET @y = 1
SELECT @x = UPPER(SUBSTRING(@x,1,1))+LOWER(SUBSTRING(@x,2,LEN(@x)-1))+' '
WHILE @y < LEN(@x)
BEGIN
SELECT @y=CHARINDEX(' ',@x,@y)
SELECT @x=SUBSTRING(@x,1,@y)+UPPER(SUBSTRING(@x,@y+1,1))+SUBSTRING(@x,@y+2,LEN(@x)-@y+1)
SELECT @y=@y+1
END
RETURN @x
END
SELECT dbo.udf_TitleCase('THE QUICK FOX jUMPED OVER tHE LAZY DOG')
Friday, October 22, 2010
Useful Shortcut Keys
In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.
Also clears the content of the active cell.
In cell editing mode, it deletes the character to the left of the insertion point.
In cell editing mode, it deletes the character to the right of the insertion point.
Also selects the last command on the menu when a menu or submenu is visible.
In a data form, it moves to the first field in the next record.
Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.
In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often theOK button).
Closes an open menu or submenu, dialog box, or message window.
It also closes full screen mode when this mode has been applied, and returns to normal screen mode to display the Ribbon and status bar again.
Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.
Selects the first command on the menu when a menu or submenu is visible.
* If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the current region and its summary rows. Pressing CTRL+SHIFT+SPACEBAR a third time selects the entire worksheet.
* When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.
Moves between unlocked cells in a protected worksheet.
Moves to the next option or option group in a dialog box.
Subscribe to:
Posts (Atom)