Replicate, Space, Patindex, Replace and Stuff functions - Part 24

Before watching this video, please watch
Part 22 – Built in string functions in sql server
Part 23 – Left, Right, CharIndex and Substring functions

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

Example: SELECT REPLICATE('Pragim', 3)
Output: Pragim Pragim Pragim 

A practical example of using REPLICATE() function: We will be using this table, for the rest of our examples in this article.


Let's mask the email with 5 * (star) symbols. The output should be as shown below.


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



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

Example: The SPACE(5) function, inserts 5 spaces between FirstName and LastName
Select FirstName + SPACE(5) + LastName as FullName
From tblEmployee

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.

Example: 
Select Email, PATINDEX('%@aaa.com', Email) as FirstOccurence 
from tblEmployee
Where PATINDEX('%@aaa.com', Email) > 0

Output:



REPLACE(String_Expression, Pattern , Replacement_Value)
Replaces all occurrences of a specified string value with another string value.

Example: All .COM strings are replaced with .NET
Select Email, REPLACE(Email, '.com', '.net') as ConvertedEmail
from  tblEmployee



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.

Example:
Select FirstName, LastName,Email, STUFF(Email, 2, 3, '*****') as StuffedEmail
From tblEmployee

Output: