This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

Me And My Respected Teacher Mr Kamal Sheel Mishra

Mr. K.S. Mishra is HOD of Computer Science from SMS Varanasi where I have completed my MCA

Me And My Respected Teacher Mr Udayan Maiti

Mr. Udayan Maiti is a senior .Net Expert and has guided many professionals of multi national Companies(MNC)

Me And My Best Friend Mr Ravinder Goel

Mr. Ravinder Goel is a senior Software Engineer and now he is working Wipro Technology

Wednesday 12 August 2015

Built String Function in Sql Server

Built String Function in Sql Server

1. Ascii:  Its return the ascii code for the given character expression

Syntax:  ASCII('character_expresssion')
Example  print ascii('A')  
Output : 65
***************** Using ASCII in  Sql Server **********************************
Example:1
declare @st int
set @st=65
while(@st<=90)
begin
print char(@st)
set @st=@st+1
end
Output : A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

65 to 90  = A to  Z
97 to 22  = a to  z
48 to 58  = 1 to  10



2. Char:  Its convert on int ascii code to character .The integer expression should be between 0-255
Syntax:  char(integer _expression)
Example  print char(66)
Output : B

3. LTRIM: remove blanks on the left hand side of the given character expression
Syntax:  LTRIM(‘character_expresssion')
Example  print LTRIM('    KUSH')
Output : KUSH

4. RTRIM: remove blanks on the right  hand side of the given character expression
Syntax:  RTRIM(‘character_expresssion')
Example  print RTRIM('TIWARI    ')
Output : TIWARI

4. Lower : convert  all the characters in the character_expression to lower case letter
Syntax:  LOWER(‘character_expresssion')
Example  print LOWER('MANOJ')
Output : manoj

5. Upper : convert  all the characters in the character_expression to upper case letter
Syntax:  UPPER (‘character_expresssion')
Example  PRINT UPPER('yadav')
Output : YADAV

6. Reverse : convert  all the characters in the character_expression to upper case letter
Syntax:  REVERSE (‘any_string _expresssion')
Example  PRINT REVERSE('yadav')
Output : vaday

7. LEN : return length of any string expression
Syntax:  Len (‘string _expresssion')
Example  print Len(' jyotirana')
Output : 10 ( 1 white space + 9 char)

8. Left:  Return the specified number of characters from the left hand side of the given characters expression
 Syntax:   Left ('Character_Expression', Inter_Expression)
Example:  SELECT Left('ABCDEF',3)
Output :  ABC

9. Right: Return the specified number of characters from the right hand side of the given characters expression
 Syntax:   Right ('Character_Expression',Inter_Expression)
Example:  SELECT  Right('ABCDEF',3)
Output :  DEF

10. CHARINDEX Return the starting position of the specified expression in characters string
 Syntax:   CHARINDEX('Expression_To_Find','Expression_To_Search')
Example:  SELECT CHARINDEX('@','kushktiwari@gmail.com')
Output :  12

10. Substring Return substring (part of string) from the given expressions
Syntax:   Substring('expression', start, length)
Example:  SELECT Substring('kush@gmail.com',6,9)
Output :  gmail.com

************************* query *************************************
select SUBSTRING('kushktiwari@gmail.com',charindex('@','kushktiwari@gmail.com'),10)
Result  @gmail.com
select SUBSTRING('kushktiwari@gmail.com',charindex('@','kushktiwari@gmail.com')+1,10)
Result  gmail.com
select SUBSTRING('kushktiwari@gmail.com',CHARINDEX('@','kushktiwari@gmail.com')+1,len('kushktiwari@gmail.com')-CHARINDEX('@','kushktiwari@gmail.com'))
Result  gmail.com



******** by this Sql function we find domain name of any emailid *********

create function GetDomainName(@emailid varchar(50))
returns varchar(50)
as
begin
declare @res varchar(50)=null
set @res=SUBSTRING(@emailid,CHARINDEX('@',@emailid)+1,len(@emailid)-CHARINDEX('@',@emailid))
return @res
end

--execute function

select dbo.GetDomainName('kushtiwari@outlook.com')

Result outlook.com
--query for how to create  table  …………………………….


create table Employee
(
SrNo int identity(1,1) primary key,
FirstName varchar(50),
LastName varchar(50),
EmailId varchar(50),
Address varchar(50)
)


select
FirstName+ ' ' +LastName as FullName ,
EmailId,
Address,
SUBSTRING(EmailId,CHARINDEX('@',EmailId)+1,len(EmailId)-CHARINDEX('@',EmailId))  as DomainName
from employee

Note: By this query we can find that how many account exist on specific domain, you can see the result in this image which are given below

select SUBSTRING(EmailId,CHARINDEX('@',EmailId)+1,len(EmailId)-CHARINDEX('@',EmailId))  as DomainName,
count(EmailId) as TotalEmailId from employee
group by  SUBSTRING(EmailId,CHARINDEX('@',EmailId)+1,len(EmailId)-CHARINDEX('@',EmailId))


Result