Built in string function LEFT, RIGHT, CHARINDEX and SUBSTRING,Replicate, Space, Patindex, Replace and Stuff in sql server

Let us assume we have table user as shown below. 

LEFT(Character_Expression, Integer_Expression) - Returns the specified number of characters from the left hand side of the given string.
forExample:

select left(FirstName,3)FirstName,left(LastName,3)LastName,left (Email,3)Email,left(cast(DOB as date),4)DOB from Users

Output:

RIGHT(Character_Expression, Integer_Expression) - Returns the specified number of characters from the right hand side of the given character expression.
forExample:

select right(FirstName,3)FirstName,right(LastName,3)LastName,right(Email,3)Email,right(cast(DOB as date),2)DOB from Users
Output:

CHARINDEX('Expression_To_Find', 'Expression_To_Search', 'Start_Location') - Returns the starting position of the specified expression in a character string. Start_Location parameter is optional.
forExample:
select charindex('@',Email,1)Indexof@ from Users where ID=1


Output=4


SUBSTRING('Expression', 'Start', 'Length') - As the name, suggests, this function returns substring (part of the string), from the given expression. You specify the starting location using the 'start' parameter and the number of characters in the substring using 'Length' parameter. All the 3 parameters are mandatory.
forExample:

select substring(FirstName,1,1)FirstName,substring(LastName,1,3)LastName,substring(Email,1,CHARINDEX('@',Email,1)-1)Email,left(cast(DOB as date),4)DOB  from Users

Output:

REPLICATE(String_To_Be_Replicated, Number_Of_Times_To_Replicate) - Repeats the given string, for the specified number of times.

forExample:

Select FirstName, LastName, SUBSTRING(Email, 1, 2) + REPLICATE('*',5) + SUBSTRING(Email, CHARINDEX('@',Email), LEN(Email) - CHARINDEX('@',Email)+1) as Email from Users

Output:

REPLACE(String_Expression, Pattern , Replacement_Value)

Replaces all occurrences of a specified string value with another string value.

forExample:

Select Email, REPLICATE(Email, '.com', '.net') as ConvertedEmail from Users

Output:

SPACE(Number_Of_Spaces) - Returns number of spaces, specified by the Number_Of_Spaces argument.

forExample:

Select FirstName + SPACE(5) + LastName as FullName 

from Users

 where ID=1

Output:

PATINDEX('%Pattern%', Expression)

Returns the starting position of the first occurrence of a pattern in a specified expression. It takes two arguments, the pattern to be searched and the expression. PATINDEX() is simial to CHARINDEX(). With CHARINDEX() we cannot use wildcards, where as PATINDEX() provides this capability. If the specified pattern is not found, PATINDEX() returns ZERO.

forExample:

Select 

Email, PATINDEX('%@gmail.com', Email) as FirstOccurence 

from Users 

where PATINDEX('%@gmail.com', Email) >4

Output:

STUFF(Original_Expression, Start, Length, Replacement_expression)

STUFF() function inserts Replacement_expression, at the start position specified, along with removing the charactes specified using Length parameter.

forExample:

Select 

FirstName, LastName,Email, STUFF(Email, 2, 3, '*****') as StuffedEmail 

from Users

 where ID in (1,2,3)

Output: