Replicate; Space; Patindex; Replace and Stuff string functions in sql server 2008 Part 24 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 24 of sequel
server in this session we'll learn about
the rest of the building string
functions that are available in sequel
server specifically we'll be looking at
replicate space fat index replays and
stuff functions before continuing with
this session I strongly recommend to
watch parts 22 and 23 of this video
series all right the first function that
we'll look at is the replicate function
now if you look at the syntax of the
replicate function it has got two
parameters string to be replicated and
how many times you want to replicate
that given string so this function
repeats the given string for the
specified number of times let's
understand this with an example if you
look at here I have the Select Clause
select and then we have this replicate
function and to this we are passing this
presume keyword that's the first
parameter and the second parameter how
many times you want to replicate this I
want to replicate this three times so
when we execute this plug-in word along
with the space next to it will be
repeated three times okay now here is a
simple practical example of the
replicate function if we look at this we
have got first name last name and email
of the person now what we basically want
to do we have seen in many of the
real-time applications they don't reveal
full email address but maybe they will
just reveal that you know using some
masks you know so basically we are
masking here the email address of a
person using five star symbols and to do
that we are using the replicate function
and using the replicate function itself
is pretty simple here look at this in
the output we want the first name last
name and you know the masked email so
first name last name we'll select it as
usual but we want to mask the email
column so first what we are doing we are
getting the two the first two letters of
the email okay and obviously if you want
a sub string of a given string we use
the substring function
and we have spoken about substring
function care index length in the
previous session in a great detail so if
you want to know how to work with that
please watch the previous session so
this first piece here it gives us the
first two letters of the email and to
that two letters we are actually
concatenated using the plus sign five
start symbols look at this this is where
we are using the replicate function I
want to replicate the star five times
okay and then to the five stars we are
then appending the @ symbol and the
domain okay a comb or BBB comm whatever
okay and again to get the @ symbol and
the domain we are using the substring
because we want the rest of that okay so
we are basically using replicate
function to mask the email one of the
practical uses of replicate function
alright the next function that we'll
talk about is the space function and
this is a pretty simple function to
understand okay now if you want a space
in a sequel query you know you basically
can use you know something like this so
if I just want a space I can say select
and you know within the single code we
can specify how many ever spaces spaces
I want for example if I want five spaces
one two three four five I have all the
five spaces there and it shows I mean
obviously you can't see a space but
that's how it is now this is a little
odd let's say if I want like 10 spaces
this becomes you know little odd to look
at that's why using the space function
is much better so when we say select
space of maybe how many ever you want I
want five space and this is more
readable as well now in the first case
you have to literally count one two
three four five you have to do that I
mean here by looking at that you can say
okay
there is going to be five spaces now if
I execute this line okay obviously we
can't see them but okay so what we
basically want to do with the space
function is you know I want to insert
you know five spaces between
the first and last name you know I want
the full name of the employee but
between their first and last name I want
five spaces how do I do that
we just select their first name
concatenate that with five spaces using
the space function and then their last
name and give it an alley as name full
name from table employee pretty simple
and obviously if you want to do the same
thing instead of using the space
function you can literally put five
spaces within single quotes here but
then that's not that readable okay so
the next very useful function that we
will talk about is the pad index
standing for pattern index now this
pattern index function returns the
starting position of the first
occurrence of a pattern in a specified
expression so in the in the given
expression okay it will find the first
occurrence of a pattern that you want
okay
in a way pad index is similar to care
index look at that pattern index
character index now both of them returns
you know the index of a string but with
pat index you have the flexibility of
using wildcards but with care index you
cannot use wildcards that's the only
difference and both of them are
extremely useful okay so here is a
practical application of this pattern
index now what we are basically doing
here is we are finding the look at the
query here so we are getting the email
and within the within the email I am
finding this pattern you know at a a a
dot-com I want you know the index the
start index of at a calm okay before
that I don't care what what is there I
mean do we have three characters four
characters is it Sam ROM John whatever
we don't care that's why we are using
the wild cuts again we have spoken about
wild cards in a great detail in the
previous sessions of this video series
so please check back if you don't know
what a wild cards and how to use them
okay so we are specifying okay you know
I want the pattern index of this
a calm but I don't care what is really
before that okay before that add symbol
and then we are using so I want to find
this pattern in this email column of the
employee table okay so obviously when we
execute that query you should see so if
you look at this without I'm executing
this query without the where clause so
when I execute without the air where
clause look at this wherever we have a a
comm at a a a.com you know we are
getting the index there the first
occurrence the first occurrence here is
one two three at the fourth position we
get that but look at this here we got
four at CCC comm if you look at this
string we don't have at a a a comm
string within that email so that why
that's why it returns zero so if the
given pattern is not found in the given
string then it returns a zero okay
and so obviously if you just want the
emails for which you have this index
number then obviously you can use the
where clause and filter that with
greater than zero and it will only give
us those emails which has got that
pattern we want which is at a comm so
that's about pad index and and it's very
much similar to cat index as I told you
all right the next function that we'll
talk about is the replace function as
the name says it's going to replace
something for us so it basically again
this function is really useful we
replace all occurrences of a specified
string with another string okay so I
have the string you know expression and
a pattern and a replacement value okay
within this original strain whatever
pattern you specify okay we want to
replace that pattern with this value
okay
so if you want a very simple example
look at this we have got first name last
name email and what I want to do is I
want to replace the dot-com with the
dotnet it's pretty simple to do all you
do is okay use the replace function of
so for the replace function you
specifies in the email column I want to
replace dot-com with dotnet it's as
simple as that and give it an early as
as converted email from table employee
let's see you know this query in action
again these functions are very simple to
use all you have to understand about is
the parameters that it expects so let's
execute this query you know the email
and the converted email calms got
converted into dotnet okay so the final
function that we'll talk about is the
stuff function you know as the name
States it's going to stuff something
okay so basically what this function
does is it's slightly different replay I
mean it's going to replay some
characters but then look at this the
stuff function it expects a string and
then within that string at the start
position you specify and for how many
characters the length okay and the
replacement expression so what what what
the stuff function going is going to do
is you can say okay start at position
two if you look at the baby have used
the stuff function here I mean it's very
clear here first let's understand the
output that we want you know we want the
first character from the email okay we
want to leave it there but then for the
next you know three characters after the
first character for the next three
characters I want to replace the next
three characters you know in this case
a.m. and at symbol I want to replace
that with five stars and then whatever
follows that we just want that okay so
basically this is another kind of
masking okay using the stuff function
and look at this if you look at the way
we have used this stuff function we are
saying okay work on the email column on
the email column start at letter to okay
which means leave the first character
but start
number two and then for the rest three
characters you know if you take Sam at
a.com
okay start at two and the next three
characters a M and AD for these three
symbols replace those three characters
with five star symbols okay and then
obviously we are giving it an alley as a
stuffed email and we have that there
okay so basically stuff function inserts
this replacement expression add the
start position specified along with
removing the number of characters that
you have specified using the length
parameter on this 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