November 4th 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	We will discuss in next article
ASCII CHAR UPPER LOWER LTRIM RTRIM REVERSE LEN LEFT RIGHT	CHARINDEX SUBSTRING REPLICATE SPACE PATINDEX REPLACE STUFF

Below is explanation for each function.

ASCII Function

Syntax:ASCII( Character_Expression )Description:It is used to return ASCII code of the supplied character expression.Input expression can be char or varchar.It returns ASCII code of first character from left hand side.It does opposite of CHAR function.Example:

SELECT ASCII('a') --97

SELECT ASCII('ab') --97

SELECT ASCII('A') --65

SELECT ASCII('0') --48

SELECT ASCII('1') --49

SELECT ASCII('aA') --97 It returns ASCII code for first character only

SELECT ASCII(' ') --32 (single space)

CHAR Function

Syntax:ASCII( Integer_Expression )Description:It is used to convert an int ASCII code to a character.Input should be between 0 and 255.It does opposite of ASCII function.Example:

SELECT CHAR(97) --a

SELECT CHAR(65) --A

SELECT CHAR(255) --ÿ

--To print all characters whose acsii code is between 0 to 255

DECLARE @Number INT

SET @Number = 1

WHILE(@Number 255)

BEGIN

PRINT CHAR(@Number)

SET @Number = @Number + 1

END

UPPER Function

Syntax:UPPER ( character_expression )Description:It is used to convert character expression into upper case character.Example:

SELECT UPPER('Welcome to ASPArticles') --WELCOME TO ASPARTICLES

SELECT UPPER('AbCd') --ABCD

LOWER Function

Syntax:LOWER ( character_expression )Description:It is used to convert character expression into lower case character.Example:

SELECT LOWER('Welcome to ASPArticles') --welcome to asparticles

SELECT LOWER('AbCd') --abcd

LTRIM Function

Syntax:LTRIM ( character_expression )Description:It is used to remove spaces from left side of character expression.Example:

SELECT LTRIM(' welcome') --removes spaces from left

RTRIM Function

Syntax:RTRIM ( character_expression )Description:It is used to remove spaces from right side of character expression.Example:

SELECT RTRIM('welcome ') --removes spaces from right

REVERSE Function

Syntax:REVERSE ( string_expression )Description:It is used to reverse the order of supplied string.Example:

SELECT REVERSE('ASPArticles.com') --moc.selcitrAPSA

SELECT REVERSE(12345) --54321

--here implicit conversion happens from int to varchar datatype and then reverses

LEN Function

Syntax:REVERSE ( string_expression )Description:It is used to count total characters in the supplied string expression.It excludes the spaces at the end of the expression (right hand side).Example:

SELECT LEN('Hello ') --5

SELECT LEN(' Hello ') --6

LEFT Function

Syntax:LEFT ( character_expression , integer_expression ) Description:It is used to return the specified number of characters from the left hand side of the supplied character expression.Example:

SELECT LEFT('Hello World',7) --Hello W

SELECT LEFT('ASPArticles.com',11) --ASPArticles

SELECT LEFT(4321,2) --43

RIGHT Function

Syntax:RIGHT ( character_expression , integer_expression ) Description:It is used to return the specified number of characters from the right hand side of the supplied character expression.Example:

SELECT RIGHT('Hello World',7) --o World

SELECT RIGHT('ASPArticles.com',11) --rticles.com

SELECT RIGHT(4321,2) --21

Breaking Barriers: How Free Cloud Gam…
Review - Know Goes Away

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

People also like

Breaking Barriers: How Free Cloud Gaming Transforms the Gaming Industry

Review - Know Goes Away

SQL-Server built-in String function