String Functions Available in SQL Server

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.