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
0 comments:
Post a Comment