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:
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:
Hello,
ReplyDeleteI 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
You have to use Mid function
Deletein the mail for example naresh@yahoo.com
ReplyDeletehere 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?
select SUBSTRING((SUBSTRING('naresh@yahoo.com',CHARINDEX('@','naresh@yahoo.com')+ 1,len('naresh@yahoo.com') - charindex('@','naresh@yahoo.com'))),1,5)
ReplyDeleteSelect Email, CHARINDEX('@abc.com', Email) as FirstOccurence
ReplyDeletefrom tblEmployee
Where CHARINDEX('@abc.com', Email) > 0