SQL-Server built-in String function Part 2

In this tutorial, I am going to explain you SQL-Server built-in String functions with example. Below is the list of most commonly used SQL-Server System defined String function.

We will discuss in this articleAlready discussed in previous article
  • CHARINDEX
  • SUBSTRING
  • REPLICATE
  • SPACE
  • PATINDEX
  • REPLACE
  • STUFF
  • ASCII
  • CHAR
  • UPPER
  • LOWER
  • LTRIM
  • RTRIM
  • REVERSE
  • LEN
  • LEFT
  • RIGHT
Click here to read more

Below is explanation for each function.

CHARINDEX Function

Syntax:
CHARINDEX ( expressionToFind , expressionToSearch, [ Start_location ] )
Description:
It is used to find index position of specified expression in a Given Expression.
Start_location parameter is optional.
Searching starts from first position if Start_location is not supplied.
Returns Zero if specified expression is not found.
Example:

SELECT CHARINDEX('@','Rahul@gmail.com')  --6
SELECT CHARINDEX('S','ASPArticles@yahoo.com')  --2
SELECT CHARINDEX('S','ASPArticles@yahoo.com',3)  --11
SELECT CHARINDEX('.','ASPArticles@yahoo.com')  --18
SELECT CHARINDEX('X','ASPArticles@yahoo.com')  --0
SELECT CHARINDEX('es','ASPArticles@yahoo.com')  --10
SELECT CHARINDEX('sp','aspasp@yahoo.com')  --2
SELECT CHARINDEX('sp','aspasp@yahoo.com',3)  --5
SELECT CHARINDEX('sp','aspasp@yahoo.com',10)  --0
--perform a case-sensitive search
SELECT CHARINDEX ( 'TEST', 'This is a Test' COLLATE Latin1_General_CS_AS)  --0
SELECT CHARINDEX ( 'Test', 'This is a Test' COLLATE Latin1_General_CS_AS)  --11

--perform a case-insensitive search
SELECT CHARINDEX ( 'TEST', 'This is a Test' COLLATE Latin1_General_CI_AS)  --11
SELECT CHARINDEX ( 'Test', 'This is a Test' COLLATE Latin1_General_CI_AS)  --11

SUBSTRING Function

Syntax:
SUBSTRING ( expression ,start , length )
Description:
It is used to find part of string in given expression.
Expression can be of character, binary, text, ntext, or image type.
Example:

SELECT SUBSTRING('asparticles.com',1,3) --asp
SELECT SUBSTRING('asparticles.com',4,8) --articles
SELECT SUBSTRING('asprticles.com',12,3) --com
SELECT SUBSTRING('admin@gmail.com',7,9) --gmail.com

Combining LEN,CHARINDEX and SUBSTRING Functions

Here, we will use len, charindex and substring functions together as shown in below example.
Example:

CREATE TABLE #Employee
(
ID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20),
Email VARCHAR(50)
)
GO
INSERT INTO #Employee VALUES('A1','B1','A1.B1@gmail.com')
INSERT INTO #Employee VALUES('A2','B22','A2.B22@yahoo.com')
INSERT INTO #Employee VALUES('A3','B333','A3.B333@facebook.com')
INSERT INTO #Employee VALUES('A4','B4444','A4.B4444@abc.com')
INSERT INTO #Employee VALUES('A5','B55555','A5.B55555@xyz.com')

--Extract name part
SELECT substring(Email, 1, CHARindex('@',Email)-1 ) AS 'NamePart' FROM #Employee
OUTPUT:
NamePart
A1.B1
A2.B22
A3.B333
A4.B4444
A5.B55555

--Extract domain part
SELECT substring(Email, CHARindex('@',Email)+1, LEN(Email)-CHARindex('@',Email) ) AS 'DomainPart'FROM #Employee
OUTPUT:
DomainPart
gmail.com
yahoo.com
facebook.com
abc.com
xyz.com

REPLICATE Function

Syntax:
REPLICATE ( string_expression ,integer_expression )
Description:
It is used to repeat the given string for the specified number of times.
Example:

SELECT REPLICATE('Hello',3) --HelloHelloHello

SELECT REPLICATE('Hello ,',3) --Hello ,Hello ,Hello ,

SELECT Email,
SUBSTRING(Email, 1, 2) + REPLICATE('*',3) + SUBSTRING(Email, CHARINDEX('@',Email), LEN(Email) - CHARINDEX('@',Email)+1) as ModifiedEmail
from #Employee
OUTPUT:
Email                   ModifiedEmail
A1.B1@gmail.com         A1***@gmail.com
A2.B22@yahoo.com        A2***@yahoo.com
A3.B333@facebook.com    A3***@facebook.com
A4.B4444@abc.com        A4***@abc.com
A5.B55555@xyz.com       A5***@xyz.com

SPACE Function

Syntax:
SPACE ( integer_expression )
Description:
It is used return number of space, specified by the integer_expression.
Example:

SELECT 'Hello' + SPACE(5) + 'World' as 'Name'  --Hello     World

SELECT FirstName + SPACE(2) + LastName as Name From #Employee
OUTPUT:
Name
A1  B1
A2  B22
A3  B333
A4  B4444
A5  B55555

PATINDEX Function

Syntax:
PATINDEX ( '%pattern%' , expression )
Description:
It is used to find 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 similar to CHARINDEX, but we can not use wild-cards with CHARINDEX where as with PATINDEX we can use wild-cards.
If the specified pattern is not found, PATINDEX returns ZERO.
Example:

SELECT PATINDEX('%art%', 'starting data')  --3

SELECT Email, PATINDEX('%.com', Email) as FirstOccurence from #Employee
OUTPUT:
Email                  FirstOccurence
A1.B1@gmail.com        12
A2.B22@yahoo.com       13
A3.B333@facebook.com   17
A4.B4444@abc.com       13
A5.B55555@xyz.com      14

--using wildcards
SELECT PATINDEX('%en_ure%', 'please ensure the door is locked') --8

SELECT PATINDEX('%[aeiou]%', 'Hello World')  --2

SELECT PATINDEX('%S%com', 'ASPArticles.com')  --2

SELECT PATINDEX('%S%com', 'ASPArticles.net')  --0

SELECT PATINDEX('%c_e%', 'ASPArticles.net')  --8

REPLACE Function

Syntax:
REPLACE ( string_expression , string_pattern , string_replacement )
Description:
It is used to replace all occurrences of a specified string value with another string value.
Example:

SELECT REPLACE('abcdefghicde','cde','xxx') --abxxxfghixxx
SELECT Email, REPLACE(Email, '.com', '.net') as ReplacedEmail from  #Employee
OUTPUT:

Email                 ReplacedEmail
A1.B1@gmail.com       A1.B1@gmail.net
A2.B22@yahoo.com      A2.B22@yahoo.net
A3.B333@facebook.com  A3.B333@facebook.net
A4.B4444@abc.com      A4.B4444@abc.net
A5.B55555@xyz.com     A5.B55555@xyz.net

STUFF Function

Syntax:
STUFF ( character_expression , start , length , replaceWith_expression )
Description:
It is used insert a string into another string.
It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Example:

SELECT STUFF('ASPArticles.com', 1, 3, 'C#')  --C#Articles.com

SELECT Email, STUFF(Email, 2, 3, '***') as StuffedEmail From #Employee
OUTPUT:
Email                StuffedEmail
A1.B1@gmail.com      A***1@gmail.com
A2.B22@yahoo.com     A***22@yahoo.com
A3.B333@facebook.com A***333@facebook.com
A4.B4444@abc.com     A***4444@abc.com
A5.B55555@xyz.com    A***55555@xyz.com


Share this

Related Posts

Previous
Next Post »