We will be going through following String function:

1. Replicate
   
2. Space
   
3. Pat index
   
4. Replace
   
5. Stuff
   

Replicate:

This function replaces the given string to the specified number of times. Syntax

REPLICATE(“STRING”,Number_of_Time)

select REPLICATE(‘SAILLESH’,3)

Let take an example of following table:

Take an example we have reporting page where we have to show the Email as first two name and then followed by (*).

In order to achieve this requirement we will make use of Replicate function.

In order to do the same we will break down our problem into smaller problems:

• First we will get only starting two character of Email using SUBSTRING
  

SELECT
SUBSTRING([Email],1,2)
FROM Student

• Next is to Show * 5 times using REPLICATE()

SELECT SUBSTRING([Email],1,2)+REPLICATE(‘*’,5)
FROM
Student

Output:

Now in order to show the rest of the characters starting from ‘@’ we will have to first calculate the how many characters are there after @. So in order to get that we will user LEN and CharIndex:

Select (LEN(EMAIL)-CHARINDEX(‘@’,EMAIL)+1)
AS EMAIL from student

This will return the length number which we will be using in SUBSTRING Function to set the length, we are than concatenating them with each other and at the end we are using SUBSTRING to get the @ and rest of the characters.

SELECT
[Name],
SUBSTRING([Email],1,2)+REPLICATE(‘*’,5)+      SUBSTRING([Email],CHARINDEX(‘@’,Email),
LEN(EMAIL)-CHARINDEX(‘@’,EMAIL)+1)
AS
EMAIL,[Course]
FROM
[SampleDb].[dbo].[Student]

Output:

Space Function:

If we want to show space in our SQL query we can make use of this function.

We want to display Name and Course column together with 2 spaces between them.

SELECT NAME+SPACE(2)+COURSE as StudentInfo FROM STUDENT

PatIndex function

PatIndex function returns the starting position of the pattern specified in the expression. The function takes two argument (pattern, Expression)

PatIndex is similar to CharIndex except PatIndex allow wildcards. In order to demonstrate the same let take below mentioned table as Source:

Now we will be showing position of the PatIndex matching pattern and using PatIndex in where clause. So I am trying to retrieve the position of all the Email which starts with ‘SHAIL’. So in order to find the same we will use wildcard % in our query.

select EMAIL,
PATINDEX(‘%SHAIL%’,EMAIL)
AS OCCURENCE FROM Student WHERE
PATINDEX(‘%SHAIL%’,EMAIL)
>0

We can even use where clause with Pat Index as shown below:

select EMAIL,
PATINDEX(‘%SHAIL%’,EMAIL)
AS OCCURENCE FROM Student

WHERE
PATINDEX(‘%SHAIL%’,EMAIL)
>1

Replace Function

Replace function replace all occurrence of specified string with another passed string. For example in Email column I want to replace all gmail to yahoo. We can make use of Replace function in order to implement the same.

Before execution:

select name,REPLACE(EMAIL,‘GMAIL’,‘YAHOO’)
FROM Student

STUFF Function

Stuff function delete the sequence of characters from the source string and then inserts another sequence of characters
into the source string at the specified position.

Now here we are trying to use Course column and will start at 2, and insert the new string in place of number of chars as shown below:

select
STUFF(course,2,3,‘****’)
from Student