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