Built in string functions in sql server 2008 Part 22 Video Lecture - SQL

Oh welcome to presume technologies i'm
venket this is part 22 of sequel server
in this session we'll learn about the
commonly used built-in string functions
that are available in sequence server
functions in sequence server are broadly
divided into two categories user-defined
functions and system functions we
haven't spoken about user-defined
functions we will be talking about them
in a later session in this session we'll
talk about all the available system
functions now if you want to see all the
system functions that are available in
sequel server expand program abilities
folder within that function system
functions and you should see it in all
the system functions available here
categorized into their own categories
depending on the functions to perform
now if if you want to you know aggregate
values then you use some of the
aggregate functions like some account
etc and if you want to work with date
and times we have date and time
functions which we'll be talking about
in a later session and similarly if I
want to work with strings
I have sing string functions today we
will be looking at some of the commonly
used string functions now we have this
ASCII string function now we use ASCII
string function to return the ASCII code
value of the given character expression
now every character has got an
Associated ASCII code value so if you
want to retrieve the ASCII code value of
a given character expression then we use
the ASCII function okay let's see how to
use that so look at this this is the S
key function
now select ASCII and look at this the
moment I open this parenthesis you know
Visual Studio sequel server
I mean sequel server intellisense shows
me okay this is expecting a ver care
datatype and expression of type ver care
and this is going to return an integer
okay so this is this is one way of
identifying what you need to pass to the
function and what it returns to you back
okay another way is if you just expand
the function itself it should tell you
okay the parameters that it is expecting
is an expression of type care or where
care and it returns an integer because
ASCII code value is an integer between 0
and 255 it's going to return an integer
for a given character expression ok so I
want the ASCII code value for capital
letter a so when we select that plus f5
65 is the S key code value for capital
letter a now what happens if I say ABC
if I pass three of the characters like
this look at this because this is
expecting a VAR cab ok ABC is aware care
so when I pass that what happens it's
going to return the ASCII code value of
the first character which is a in this
case now if I just say get me the ASCII
character value of BC then it's going to
give me the ASCII code value of letter B
which is 66 okay so we use s Kico I mean
ASCII function to return they ask a code
value of the given character expression
the next function that we commonly use
is the character function ok now what
does this character function do it
converts an ASCII code to a character ok
now see the ASCII character reverses
what ASCII s key function does no ASCII
function returns the ASCII code value
whereas the character function returns
the character associated with the ASCII
code value and we know that the ASCII
code values are between 0 and 255 ok and
we know the ASCII code value for capital
letter a is 65 now let's say I want to
print alphabets from A through Z how do
I do that you know I can make use of
this ASCII code and then create a loop
and then print them let's see how to do
that now let's say if I use the cat
function look at this when I expand and
the intellisense it shows it is
expecting an integer expression and
integer and then what is it going to
return to you it's going to return a
character okay so for example if I pass
65 you know that 65 is the ASCII code
value of capital letter A
so when I press you know execute this I
should get letter a now let's say I want
to print the alphabets capital letter
alphabets from A through Z how do I do
that I can use a while loop to do that
and the care function let's do that so
declare a variable of type integer let's
call that start integer initialize that
to maybe 65 because capital letter a
starts at 65 so 65 and then what I want
to do from 65 there are 26 alphabets in
English language so 65 plus 26 is 91 so
91 until 91 okay so while at start is
less than or equal to 90 begin and what
do we want to do we want to print the
character associated with that ASCII
code value so what we can do is select
care of instead of hard-coding the value
what we want to do we want to pass this
integer variable so when we pass this
integer variable you know it looks the
first time it will be 65 so it will
print capital letter a next we need to
increment that so set at start is equal
to at start plus 1 so we increment it by
1 6666 as the ASCII code value of
capital letter B so that gets printed
out so instead of selecting it will just
print it okay so let's execute this and
see what's going to happen it should
print capital letter A through Z okay so
you can see ABCD printed until Z now
let's say I want to plant small letters
from A through Z how do I do that well
you simply find the ASCII code value for
small a so press F 597 is the ASCII code
value of small a so 97 plus 26 is equal
to 123 so what we want to do we want to
start at 97
and we want to loop through 122 and
print that so this time when we execute
this query it's going to print all this
small cap I mean small alphabets okay
similarly if I want to print numbers 0
till 9 how do I do that
get the ASCII code value of 0 which is
48 so start at 48 and then 48 plus 10 is
58 so 58 and then print that that's
about it so 0 till 9 you know maybe we
want to say 57 because there is one
extra colon printed ok
so this is how we can make use of the
character function to print you know
alphabets whatever numbers etc okay and
then the other important functions that
we commonly use is L trim you know the
name itself is saying the L trim left
trim trim on the left side for a given
character expression it's going to
remove the white spaces on the left hand
side of that character expression okay
let's see that in action so for example
let's say I have something like this
select there is a name ok I have some
spaces there hello now when we execute
this it just shows that hello with the
you know white spaces on the left hand
side now let's say I want to remove
those white spaces so if you want to
remove the white spaces on the left hand
side of the given string you can use the
L trim function and pass the string to
that it's going to return you know a
string that has the white spaces removed
so we have the white spaces removed
there okay
now let's look at a practical example of
applica applying this so select star
from TBL employee if you look at the
rows in this table look at the first
name you know some of the names in this
table have got you know spaces in front
of them now let's say I want to remove
all of the spaces in front of them how
can I do that I can make use of the cell
trim function so I want to say I want
first name and maybe middle name and
last name columns okay now when we
execute this we get all those columns
where the problem is look at this they
are having these white spaces in the
front and they don't look good so if you
want to remove the white spaces you can
simply use the elgyn function and it
should knock of those white spaces at
the beginning of the name and obviously
this column name we have lost it so we
can give it an alias name if you want as
first name and you execute that you
should get the first name as expected
and similarly along the same lines we
have our stream if you want to remove
the white spaces on the right hand side
of the given character expression then
you can make use of our trim function so
let's see how to do that so let's say
there is this hello word and on the
right hand side I have white spaces now
if I want to remove those spaces there I
can make use of our trim plus f5 so it
removes the white spaces on the right
hand side we can't see that but it trust
me it removes them now let's say let's
execute this now these names have got
you know white spaces on the right hand
side as well okay so if you want to
remove the white spaces on the right
hand side you can use our trim function
let's see them first now let's say I not
only want the first name middle name and
last name isle oh I also want their full
name okay the first name and then space
middle name space last name how do I get
that first name class give a small space
and then maybe middle name and then give
a space and then last name so
essentially what we are doing here is we
are concatenated the first name middle
name and last name together using the
plus sign so now when we execute this
you should see the difference look at
that this is going to be our full name
the last column let's give it an alley
as called as
full name so if you look at this now we
have the full name but look at this
because of the spaces on the left and
right side of the first name they don't
look right okay so what we want to do is
knock off the white spaces on the left
hand side of the first name and the
white spaces on the right hand side of
the first name so that look at this
toward Kutner has got a lot of white
space in on the right hand side so how
do we remove both the left and right
side white spaces use L trim and our
trim together okay so if you pass the
first name here to L trim what it's
going to do it's going to remove the
white spaces on the left hand side and
then return the string for the return
the string remove the white spaces on
the right hand side as well so you're
passing one function inside another
function so now when we execute this
look at what's going to happen for the
output it appears as we expect first
name middle name and last name okay so
we can use ultram and are trained
basically to remove white spaces on the
left hand side and on the right hand
side of the given character expression
and similarly if you want to convert you
know letters into lowercase or uppercase
you can use lower and upper functions
okay let's see them in action let's say
I want all the first names in the
uppercase so what you can do you can use
the upper function so upper of this
first name okay and similarly let's do
that in a lawyer for last name so last
name is going to be converted into
lowercase okay so let's execute this so
look at that all first names are
converted into uppercase all last names
are converted into lowercase now since
you're using a function you will lose
the column name if you want the column
name use the Elias there all right so
that's about upper and lower functions
and we have reverse function as well if
you want to reverse all the characters
in the given string expression you use
reverse function let's say I want the
names the first names of the function
you know the first name of the person to
be reversed Sam should be s am should
become mas how can I do that use the
reverse function so when you use the
reverse function look at this I'm
chaining all these functions together
okay
so reverse so what's going to happen
it's going to reverse that Sam name mas
ma are ROM okay it's going to reverse
that okay interestingly it has again
reintroduced that white spaces but you
know how to get rid of them and then
finally length function length function
can be used to calculate the length of
the given string okay so if you look at
the name of the person you know the
first name of the person let's get the
first name from TBL employee so if you
look at the first name now I want the
first name and the number of characters
in their first name how do I do that
okay this is the first name itself so if
I want to find the length you know the
number of characters in their first name
I can use length function pass it the
name of the column that's all and maybe
you can give it an alias name maybe
total characters so when we execute this
you should see the first name and the
total characters and U and E and now you
should understand look at this there are
only three characters but I'm getting
five characters why is that because
there are white spaces at the beginning
of the string so two white spaces that's
why it's giving including those white
spaces okay so if you don't want to
count the white spaces then what you
need to do is you need to first are all
trim that remove the white spaces at the
beginning of the string now when we
execute this it should give us the
correct count of the letters okay three
three
okay so this is correct now on the right
hand side if you want to remove okay
look at this the count function I mean
the length function returns the count of
total characters in the given string
expression excluding the blanks or white
spaces at the end of the expression even
if there are white spaces at the end of
the expression it doesn't count them but
if they are at the beginning or in the
middle of the given string then it
counts them okay
so these are some of the you know
commonly used string functions we will
be talking about the rest of the
commonly used string functions like
carry index substring etc in the next
video session on the slide you can find
resources for asp.net and C sharp
interview questions that's it for today
thank you for listening
have a great day