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:

5 comments:

  1. Hello,

    I have used SUBSTRING query in MS access (where we can write SQL query in MS access) but it was not executed. i dont have SQL server.

    Please help me on this.

    Thanks
    Shyam

    ReplyDelete
  2. in the mail for example naresh@yahoo.com
    here we know how to get after that "@" result is yahoo.com
    i want to know about i need only "yahoo" remove "com" how it is?

    ReplyDelete
  3. select SUBSTRING((SUBSTRING('naresh@yahoo.com',CHARINDEX('@','naresh@yahoo.com')+ 1,len('naresh@yahoo.com') - charindex('@','naresh@yahoo.com'))),1,5)

    ReplyDelete
  4. Select Email, CHARINDEX('@abc.com', Email) as FirstOccurence
    from tblEmployee
    Where CHARINDEX('@abc.com', Email) > 0

    ReplyDelete

It would be great if you can help share these free resources