00:00:00
salamualikum' and her everyone we are
00:00:03
now in lesson three introduction to
00:00:06
functions but - at the end of this
00:00:09
lesson students should be able to
00:00:11
discover instead of functions available
00:00:13
in SQL we will take a look at character
00:00:16
functions the continuation and also
00:00:18
number functions in the previous lesson
00:00:22
we have to look up until in string now
00:00:25
let's take a look at left back left pad
00:00:28
means bets the left side of character
00:00:31
string resulting in a right justified
00:00:33
value left by requires three argument a
00:00:36
character string taught the number of
00:00:38
characters in the pattern string and a
00:00:40
corrector to pad with and we also have
00:00:44
the right pad right pad that's the right
00:00:46
hand side of a character string
00:00:48
resulting in the left justified value
00:00:50
the arguments is still the same next I
00:00:55
can look at left pad example if we take
00:00:59
the left but will have three different
00:01:01
Inhumans here the first one is the
00:01:03
character string the second one is the
00:01:05
size and the third one is the character
00:01:09
to pad with okay so if you take a look
00:01:11
first thing first we will take a look at
00:01:13
the size the size say we have 10 1 2 3 4
00:01:17
5 6 7 8 9 10 so we have 10 size then if
00:01:24
we have left pad means we need to feel
00:01:27
the right hand side first we feel the
00:01:29
right hand stuff in the first argument
00:01:30
with our character so what is our
00:01:32
characters huh means how much it will
00:01:34
take 5 so means 1 2 3 4 5 will be
00:01:39
deleted and we will change with hommies
00:01:42
then we have that the 5 spaces is in it
00:01:44
so now the first pacers we will replace
00:01:48
with the argument cadet argument is s3
00:01:51
so how many s you will get 1 2 3 4 5 so
00:01:55
means now we have what we have here is 5
00:01:58
different history with characters
00:02:02
hobbies
00:02:03
ok so let's try to as a cue this one
00:02:07
see this is the result we'll get 1 5 s
00:02:09
rigs and 5 4 characters so if the
00:02:12
character here is Ali Ali will take only
00:02:15
3 so means I'll use the only 3 so we
00:02:19
have another extra 2 asterik here so
00:02:22
let's take a look and this is the result
00:02:26
after our if we change our our first
00:02:30
string here Ali
00:02:31
[Music]
00:02:33
same goes with Iraq patio if rapper
00:02:37
means we feel the size from the left
00:02:39
hand side first for example here we have
00:02:42
right back alley 10s trick so right back
00:02:48
filter left hand side first alley then
00:02:50
we have 10 size means we have another 7
00:02:53
spaces left so we replace with s3 ok so
00:03:00
again overall is 10 the the first 3
00:03:05
characters are coming from Ali then the
00:03:07
red is coming from the yesterday so if
00:03:09
you as a cute this is what we will get
00:03:12
this one is very useful if we want to
00:03:15
represent the characters with something
00:03:19
else ok sometimes we want to mask the
00:03:21
full name so we can do this part
00:03:25
let's take a look on the implementation
00:03:27
on left pad or right pad in in our HR
00:03:31
schema so let's say we have this
00:03:33
question for every 2000 in a salary
00:03:35
replace it with a hash for example if
00:03:39
the salary is 4000 that employee will
00:03:42
receive 2 hash so if it a clue here if
00:03:45
salary equals to 4000 they will get 2
00:03:48
hash how to how to do the calculation it
00:03:51
will be salary
00:03:52
divided by 2000 for example the salary
00:03:56
now is 4,000 4,000 divided by 2000 then
00:04:00
we will gather the two spaces here so
00:04:04
this is actually for the size so now
00:04:05
let's the clue under implementation
00:04:07
select last name
00:04:11
celery then we do the left back left fat
00:04:16
we have three different arguments isn't
00:04:18
it
00:04:18
okay oops we have three different
00:04:21
arguments so let's take a look first
00:04:23
from employees so now we know that the
00:04:27
first argument is for the Seuss
00:04:30
character then let's take you on the
00:04:32
sizer second a given celery we divide by
00:04:35
two thousand and we want to replace
00:04:38
everything with hash press everything
00:04:42
with hash how about the first argument
00:04:44
let's say if we put the first argument
00:04:46
asked space okay so let's look at the
00:04:49
implementation for salad last name
00:04:50
celery left bad space celery divided by
00:04:55
2,000 as the size then de replace with
00:04:57
hash so if we execute this one we will
00:05:01
get wrong answer actually just a clear
00:05:03
six thousand supposedly if your salary
00:05:07
is six thousand you will have three hash
00:05:08
but why this happened let's take a look
00:05:11
six thousand divided by two thousand
00:05:15
will have how many spaces we have three
00:05:17
so now supposedly we get it correct
00:05:20
isn't it great but so we have three
00:05:21
spaces but our first argument we put a
00:05:24
space here so the first argument is
00:05:27
space left by we fulfill the right hand
00:05:29
side so the first argument is space then
00:05:32
another two we replace with hash that's
00:05:35
why you get only two in the end okay you
00:05:38
get two in the end so if you take a look
00:05:39
inside here there's a hash hash space so
00:05:43
it is incorrect so what can we do so the
00:05:45
first argument may be you can put as
00:05:47
hash so if we do it this way
00:05:50
six thousand divided by two thousand we
00:05:53
get three spaces can we get three
00:05:55
different three three different spaces
00:05:58
so the first argument we fulfill F / for
00:06:01
fedora hands away
00:06:02
the first argument first argument is
00:06:03
hash then how about the first a second
00:06:07
argument we replace with that argument
00:06:09
is innate hash hash then we will get
00:06:12
three different hash for six thousand I
00:06:15
becomes correct already so let's try to
00:06:17
execute this one
00:06:19
see 6000 we get 3 different hash means
00:06:23
it is correct already but what if we
00:06:26
really want to put a space at the first
00:06:29
argument here means in our sizer we need
00:06:32
to plus 1 why do we need to plus 1 here
00:06:36
because of let's take a look 6,000
00:06:39
divided by 2,000 is 3 we plus 1 we will
00:06:43
get it is equals to 4 so now our space
00:06:46
we have 4 different spaces put a left I
00:06:50
put the right hand side with the first
00:06:51
argument for argument is space so we
00:06:54
don't see anything a space then how
00:06:56
about the other three we replace with
00:06:58
hash then if you ask you this one we
00:07:01
kept the Koreans already six thousand we
00:07:03
get put a three different hash so it is
00:07:09
up to you want to use left Pat all right
00:07:11
Pat it will be the same okay the answer
00:07:13
will be the same six thousand we still
00:07:16
have three hash let's take a look at the
00:07:23
next character functions create
00:07:26
manipulation functions it is trim it
00:07:29
removes all specified characters from
00:07:31
either the beginning the end of both
00:07:34
beginning and end of strings for replace
00:07:39
it replaces a sequence of characters in
00:07:43
a string with another set of characters
00:07:47
there's one thing to not here please use
00:07:50
column Elias as it is important to hire
00:07:53
certain details from the SQL while you
00:07:56
are doing their character manipulation
00:07:58
functions or any functions
00:08:01
testicle at trim the trim will remove
00:08:04
the specific creditors is either from
00:08:06
beginning the end of both beginning and
00:08:09
end so if we tackle a this example what
00:08:12
if we have the string of ABC ba now we
00:08:16
would like to trim leading a from ABC ba
00:08:19
from the world this door because this
00:08:22
ABC be a star coming from any table so
00:08:25
let's take a look at leading a trim
00:08:27
leading a so if we have a b c ba if we
00:08:31
trim leading it will be b c ba because
00:08:34
the leading a here will be removed and
00:08:37
if we want to remove the end of our
00:08:40
string since ABC ba here at the end of
00:08:44
the string is a so we trim trailing a
00:08:48
from ABC ba so we we want to remove this
00:08:51
it there is aa b a b c d so if it has a
00:08:55
cute this is what we get a b c p we
00:08:58
remove this here the trailing a what if
00:09:02
we want to remove the leading a and
00:09:06
trailing a here so what we can do is
00:09:08
select trim both a from ABC ba so there
00:09:13
is a OB d c b why because these leading
00:09:18
a and this trailing a will be removed so
00:09:22
if you as a cute we'll get DC be test
00:09:25
the trim
00:09:25
[Music]
00:09:27
let's continue with replace we replace
00:09:31
we want to replace a sequence of
00:09:33
character in a swing with another set of
00:09:35
character for example our character say
00:09:39
our swing here is check and chew now I
00:09:41
want to replace G with BL okay so this J
00:09:46
will be replaced with PL this chair also
00:09:50
be replaced with BL from the world why
00:09:52
because check and shoe is coming it's
00:09:54
not coming from any table so if we
00:09:56
execute this one what we will get black
00:09:58
and blue white black and blue because
00:10:00
she has been replaced by the L jewel
00:10:04
this J into also is replaced by BL let's
00:10:07
take a look at another example let's try
00:10:10
to replace a character's inside our last
00:10:16
name last name we want to replace a with
00:10:21
a streak for example a with s3 ok
00:10:26
everything is not coming from the world
00:10:28
why why not dwell because of last name
00:10:31
is coming from table employees so now
00:10:33
all a or small actor inside last name
00:10:37
would be replaced with s3 so if we ask
00:10:40
you this one take a look a bell because
00:10:43
there's no small letter A and D also a
00:10:46
concern that will add this one the real
00:10:49
last name okay let's let the last name
00:10:50
or so that you can see select last name
00:10:53
replace last name so by the be a stirry
00:10:57
ID as to it because we replace molester
00:11:00
a with a stirry so let's replace so we
00:11:05
have finished with all of our characters
00:11:07
function but you need to remember one
00:11:10
thing when we do all the functions here
00:11:13
if we don't give a column Elias declare
00:11:16
our column heading here everything here
00:11:18
is shown on what what are we doing with
00:11:20
our SQL so it's better to give her
00:11:23
column Elias for each and every function
00:11:27
that you you have done to the SQL so
00:11:30
that people will not see what's actually
00:11:32
the processing behind it we hide from
00:11:35
user so instead of having all these
00:11:37
replaces now we have replacement
00:11:41
let's move on to number function in SQL
00:11:45
you have three different number
00:11:46
functions the first one is round round
00:11:50
means we want to round value to a space
00:11:52
specified decimal for trunk we want to
00:11:55
truncate value to a specified decimal
00:11:57
and for more 2 returns remainder of
00:12:00
division let's take a look at Brown
00:12:05
select run 45.92 3 to 2 decimal place so
00:12:09
we want only two decimal places this is
00:12:12
positive positive you go to the right to
00:12:14
decimal place here and if you take a
00:12:17
look at a third decimal place I said
00:12:18
this is not more than 5 so of course we
00:12:21
can add 1 to 2 here so there is a low B
00:12:24
44 9 to the second one 45.92 3 to 0
00:12:28
decimal place so means we don't want any
00:12:31
decimal places but before they have to
00:12:33
glue this is 9 so means we to add 1 here
00:12:35
so it will be 46 and take a look at 44 9
00:12:39
2 3 negative 1 means we go to the left
00:12:42
negative fun go to left minhu means we
00:12:44
want to find nearest and dearest and
00:12:46
mean like 10 20 30 40 50 and so forth
00:12:51
and if we take a look here 45 here means
00:12:53
5 and more so you will be 50 if you go
00:12:56
to the left to the nearest n so if you
00:12:59
as accurate this is the answer that we
00:13:00
get 45.92 46 and 50 but what if I change
00:13:05
this number what it become 43 point 1 2
00:13:12
6 sorry 1 2 5 ok 43.1 to 5 to 2 decimal
00:13:18
places
00:13:19
ok this one point the same 45.92 3 and
00:13:24
this one the same number also let's take
00:13:28
a look 43 to 1 to 5 to 2 decimal places
00:13:31
two decimal places 42.1 2 however this
00:13:33
is 5 we need to add 1 to 2 here so ub40
00:13:36
2.13 this one 43 don't want to five to
00:13:40
zero decimal places so if you take a
00:13:42
look this one is 1 so when this is 1 we
00:13:46
cannot add one so there is all be 43 and
00:13:48
if we take a look 1 2 5 2 negative 1
00:13:51
here toast to the left kay 43
00:13:54
it is not more than five so there is a
00:13:56
OB for the nearest end for these 43 is
00:13:59
40 because it is not 45 and above so you
00:14:02
will be 40 so let's I secure it and this
00:14:06
is the answer 40 3.13 just now 43 + 40 +
00:14:12
take a look at trunk trunk it
00:14:15
we will truncate value to specify the
00:14:16
similar but you need to remember one
00:14:18
thing
00:14:19
trunk it can never go up together the
00:14:21
number of trunk a can never go up if you
00:14:23
don't want that value reaches remove for
00:14:26
example 45.92 3 we want 9 to 10 to 3 we
00:14:31
wanted two decimal places so we just
00:14:33
remove this one so you'll be 45.92 we
00:14:36
didn't consider whether it is 5 or not
00:14:38
okay more than 5 or not so if we take a
00:14:40
look here truncate 45.92 t20 there's no
00:14:43
pieces so we don't want the decimal
00:14:45
places we just remove the decimal then
00:14:47
it will be 45 for truncate to negative
00:14:50
one nearest and we go to the left isn't
00:14:53
it 45 so the nearest 10 to 45 will be 44
00:14:57
truncate because it will never go up
00:15:00
okay so if he has a queue this one 45.92
00:15:03
45 and 40 let's change the number to
00:15:08
49.99 9 okay 44 49.99 - all of the
00:15:14
number here less the glue
00:15:18
[Music]
00:15:22
okay so 49.99 nine to two decimal places
00:15:26
we will get only 49.99 as I said we
00:15:29
don't consider the next number is five
00:15:32
or not five or more okay
00:15:34
same goes with this one 49.99 if we
00:15:37
truncate to zero decimal place it will
00:15:39
be 49 same goes with this one 49.99 i
00:15:42
will truncate to the left near as 10 49
00:15:44
it will be 40 also you can never go up
00:15:47
so if we take a look
00:15:49
yes sir 1499 4940 so that's truncate the
00:15:55
last number function is about mod mod is
00:15:58
we want to find the remainder of
00:15:59
division for example here select last
00:16:02
name salary mod salary to five thousand
00:16:06
from employees wage of ID equal to s
00:16:08
arab so means if the salary is 11,000 so
00:16:13
if this area is 11,000 Wiimote by 5,000
00:16:17
so we divide by 5,000 how much is the
00:16:19
remainder that we have 1000 because 5000
00:16:23
times 2 we have 10,000 so the remainder
00:16:25
is 1000
00:16:27
okay so if the salary is 10000 10000 we
00:16:31
divide by 5000 there's no remainder
00:16:33
isn't it ok so by right the monty were
00:16:36
to find the remainder of the division so
00:16:38
if you as a cute take a look if if your
00:16:41
salary is 10000 the remainder is 0 if
00:16:43
your seller is 11,000 do we care leaven
00:16:46
thousand here's 11,000 then the
00:16:49
remainder is 1,000 I guess that's all
00:16:53
for now see you again in the next part
00:16:55
thank you
