November 5th 2017

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 article	Already 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('@','[email protected]') --6

SELECT CHARINDEX('S','[email protected]') --2

SELECT CHARINDEX('S','[email protected]',3) --11

SELECT CHARINDEX('.','[email protected]') --18

SELECT CHARINDEX('X','[email protected]') --0

SELECT CHARINDEX('es','[email protected]') --10

SELECT CHARINDEX('sp','[email protected]') --2

SELECT CHARINDEX('sp','[email protected]',3) --5

SELECT CHARINDEX('sp','[email protected]',10) --0

--perform a case-sensitive search

SELECT CHARINDEX ( 'TEST', 'This is a Test' COLLATELatin1_General_CS_AS) --0

SELECT CHARINDEX ( 'Test', 'This is a Test' COLLATELatin1_General_CS_AS) --11

--perform a case-insensitive search

SELECT CHARINDEX ( 'TEST', 'This is a Test' COLLATELatin1_General_CI_AS) --11

SELECT CHARINDEX ( 'Test', 'This is a Test' COLLATELatin1_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('[email protected]',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 INTIDENTITY PRIMARYKEY,

FirstName VARCHAR(20),

LastName VARCHAR(20),

Email VARCHAR(50)

)

INSERT INTO#Employee VALUES('A1','B1','[email protected]')

INSERT INTO#Employee VALUES('A2','B22','[email protected]')

INSERT INTO#Employee VALUES('A3','B333','[email protected]')

INSERT INTO#Employee VALUES('A4','B4444','[email protected]')

INSERT INTO#Employee VALUES('A5','B55555','[email protected]')

--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

[email protected] A1***@gmail.com

[email protected] A2***@yahoo.com

[email protected] A3***@facebook.com

[email protected] A4***@abc.com

[email protected] 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

[email protected] 12

[email protected] 13

[email protected] 17

[email protected] 13

[email protected] 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

[email protected] [email protected]

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

[email protected] A***[email protected]

The Ultimate Guide to Cloud Gaming: D…
Ø§Ø³ØªÙƒØ´Ù Ø£ÙØ¶Ù„ ÙƒØªØ¨ Ø§Ù†Ø¬Ù„…
best projectors for home
A List of the Best College Graduation…
Key Tips for Renting an Electric Vehi…

This post first appeared on ASPArticles, please read the originial post: here