REPLICATE(String_To_Be_Replicated, Number_Of_Times_To_Replicate) - Repeats the given string, for the specified number of times. It takes two arguments:
- string_expression: the string to be repeated.
- integer_expression: the number of times to repeat the string.
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.
It takes one argument:
- integer_expression: the number of spaces to return.
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. It takes two arguments:
- pattern: the pattern to search for. The % symbol can be used as a wildcard to match any character(s).
- string_expression: the string to search within.
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.
The REPLACE() function substitutes a new substring for every instance of a
substring found in a string. The search is case-insensitive, please note.
Consider examining the STUFF() function as well. It takes three arguments:
- string_expression: the text in which the replacement is to be made.
- string_pattern: the text to be replaced.
- string_replacement: the replacement text.
Syntax: REPLACE (string_expression, string_pattern, string_replacement)
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.
We replace a substring of a string that is a given length with a new string
by using the stuff function. REPLACE: As its name suggests, the replace
function swaps out every instance of a given string value for a different
string. It takes four arguments:
- string_expression: the string in which the replacement is to be made.
- start: the starting position where the replacement will begin.
- length: the number of characters to replace.
- string_replacement: the replacement string.
Example:
Select FirstName, LastName,Email, STUFF(Email, 2, 3, '*****') as StuffedEmail
From tblEmployee
Output:
