We will be going through following String function:
- Pat index
This function replaces the given string to the specified number of times. Syntax
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
- Next is to Show * 5 times using REPLICATE()
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:
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.
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 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.
AS OCCURENCE FROM Student WHERE
We can even use where clause with Pat Index as shown below:
AS OCCURENCE FROM Student
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.
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: