LEFT; RIGHT; CHARINDEX and SUBSTRING functions in sql server Part 23 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to plane technologies I am
blanket this is part 23 of sequel server
in this session we'll talk about a few
more commonly used building string
functions available in sequel server
specifically we will talk about
left-right carry index and substring
functions and we'll also look at a real
time example of using these string
functions now before continuing with
this session I strongly recommend to
watch parts 11 and 22 of this video
series okay so what is left function
left function returns the specified
number of characters from the left hand
side of the given character expression
let's understand what we mean by this
so select let's say I have a string
called ABCDE F okay now what I want to
do is I want to retrieve the first three
characters from the left hand side of
the string okay so I can use the left
function for that okay so select left
and if you look at the intellisense that
is available in sequence over it is
expecting an expression of type and
we're care so let's pass this there okay
and then the next parameter is the
number of characters that you want from
the left hand side of this expression
let's say i want three characters so
when we try that we should get ABC on
the other hand if you want maybe four
characters just passing four you should
get also I mean get D as well okay now
so right is the inverse of left okay
left function returns the specified
number of characters from the left hand
side of the given character expression
whereas right returns the specified
number of characters from the right hand
side of the given character expression
okay so if you want let's say de F okay
then all you do is instead of left you
will say right and from the right side
let's say I want only three characters
so you get the last three characters
which is de f so we get that
okay so those are left and right
functions in sequel server now another
very useful function in sequel server is
the care index function at first it may
it may sound you know a little
complicated to use that but it's pretty
simple okay if you look at this function
it has got three parameters okay
the expression to find expression to
search and start location let's look at
an example which makes it clear now
let's say I have an email address
something like this maybe Sarah at a a
a.com so a a a.com is the domain now
let's say within this email I want to
find the index of this @ symbol how do I
do that I can make use of the care index
function okay so select I can use the
care index function and if you look at
the care index one function it expects
three parameters you know the first
parameter is the expression that you
want to search for it could be a string
or a character now in our case it's a
character we want to search the @ symbol
so I want to search the @ symbol and
then the next parameter is another
expression of type fair care this is
nothing but the expression which you
want to search so I want to search for
this @ symbol I want to find the index
of this @ symbol within this given
expression okay and then the third
optional parameter is the starting
position so if you look at that the
third parameter is the start location
now do you want to start at the
beginning of this expression or
somewhere in between
okay now let's assume that you have
maybe two or three at symbols in that
given expression and you want to find
the last one okay so in those
circumstances you can use you know the
starting position okay so let's say in
our case the starting position is one
and that's optional if you don't specify
that parameter it's going to anyway
started you know position one of the
strengths so now when we execute this we
should get the index of the @ symbol
which is five so if you look at this one
two three four five so the index of at
symbol you know is at the fifth location
okay so we get the index of that letter
so care index is basically used to
return the starting position of the
specified expression in a character
string and keep in mind start location
is optional all right now another very
useful function is substring as the name
itself says sub string I want a part of
the string so substring basically
returns part of the string from the
given expression now look at this if you
want a part of a string from the given
string then obviously you will have to
tell okay the string itself from which
you want that part and then where you
want to start and how many characters
you want okay so those are the three
parameters here the actual expression
where do you want to start and and how
many characters you want okay let's see
that in action now let's say if you look
at this you know email address Ericom
what I want this I want only the domain
part okay here the domain is a a a.com I
want just the domain part how do I do
that I can use substring okay so select
I can say substring of the expression
the expression is nothing but this email
and then you you want to specify the
starting position the starting position
is 1 2 3 4 5 okay
so you don't want anything until here
you want you know starting at position 6
so I want starting at position 6 and
then how many characters 3 4 5 6 7
characters so 7 so what is this going to
do this is gonna give us that substring
from this entire email so when we
execute this we should get only the
domain part okay so these two functions
are extensively used care index and sub
strings especially when you want to
manipulate strings so substring function
returns substring apart from the given
expression okay
now if you look at the way we have used
this function
we have hard-coded these values you know
at which position do you want to start
and how many characters do you want now
if you remember we want to start it I
mean how did you say that we want to
start at position six you know because
the @ symbol starts there we have
counted that and then we specified okay
after the @ symbol position you know
from that position onwards we want the
substring okay but this is a little
dangerous let's assume you know an email
address is of like something like maybe
Pam which is only three characters Pam
at BBB calm so if you look at this email
address it has got only three four
records now if I pass this in him pass
this email address to this substring
function look at what's gonna happen it
doesn't give us exactly what we want
we only got B be calm it knocked off one
letter that's because you want to start
you know you want the substring from one
two three four from the fifth location
okay but here it's the sixth location
that we have specified now when we do
this we get that correctly so that's the
issue with hard coding so instead of
hard coding what you can do basically
you can use this care index function
which will calculate you know the index
dynamically so you want the care index
of whatever so I want the care index
here it is Pam at BBB com so instead of
hard-coding that we can just pass the
email address and now when we execute
this we should get the same result
except that okay and another thing look
at this
since we have hard-coded that okay and
if you look at this one we are actually
getting rid of M and we got this extra
letter at symbol there and to get that
because we don't want to start at that
position instead we want to add one
because the care index of that symbol
will give you one two three four but you
don't want to start at fourth location
you want to start at fifth
location so add one to that so now when
you execute this you should get what you
expect okay and similarly you can also
you know dynamically calculate the
number of characters you want how can
you calculate the number of characters
you want fit in that string look at this
you know in this string I I want to
start at this location okay and that you
have specified using care index okay and
the next thing that you have to tell
okay from this location how many
characters do you want okay now if you
want to get the total number of
characters that are available here don't
you think if you get the total length of
this string and then subtract the care
index of that symbol because the care
index of that symbol what will it give
you it will give you one two three four
and the total length four plus seven
eleven okay from eleven subtract four
okay which will give us seven so we can
say okay I want the care index of this
one which will give us the care index of
this symbol and I want the total length
of the string so if you want the total
length of the string what you can do you
can use length function and we have
spoken about this in the previous
session so length function of what is
that of the email and then from that
length we want to subtract the care
index
of the at symbol
okay so now if we execute this now we
don't have those values hard-coded now
this select statement should work with
any emails look at that we get the exact
same output now at the beginning it may
sound a little complex okay there are a
lot of function calls here and it sounds
a lot of confusing but trust me it's
very simple if you understand how care
index substring and length function
works it's just that instead of
hard-coding the values we are calling
one function inside another function
okay now this forms the basis for the
simple real time example that we have
let's say I have our employees like this
the first name last name and their email
and they want you know the tutor by
domain okay for example in gmail.com how
many how many users are there with
gmail.com domain how many users are
there with aol.com okay I want to find
that and to do that we use exactly the
same logic that we have done here but
instead of you know hard coding the
email address like this we will be
passing in the email column from our
table okay so let's see how to do that
so when we execute this query we have
all the employees here all we want is an
output like this okay totally me I mean
email domain and the total count alright
so what do we want in our output we want
the email domain first so select so we
want the email column in the output but
we don't want the complete email we just
want the domain part okay and remember
to get the domain part we have already
written this substring function so this
will give you the domain part so copy
that but then wherever you have
hard-coded this email we want to pass in
the name of the column it's as simple as
that so email column
pass it there
okay so from where we want from TBL
employee table okay so when we execute
that we should get all the domains and
you want to give a meaningful name for
that as email domain maybe so when we
execute that we get the email domain but
we are getting all of the domains now if
you have watched part you know 11 of
this video series where we have spoken
about group by now it should be pretty
simple to group those emails by the
domain right now you can just group them
by this particular column and you're
done so the next thing that we want is
the count of employees count off
maybe email as total and then since you
are using count aggregate function you
want to use group by so I want to group
by by this expression
okay
invalid column name count of email okay
so you should get the output that we
expect email domain and the total okay
and there are the other ways of
achieving this one but since we are
talking about string functions I thought
it's better to use those functions and
achieve this output on this slide you
can find resources for a speed or net
and c-sharp interview questions that's
it for today thank you for listening
have a great day