HI WELCOME TO Sirees

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

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

ExampleSELECT REPLICATE('Kansiris', 3)
OutputKansiris Kansiris Kansiris

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:

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.