Saturday, 4 November 2017

SQL-Server built-in String function

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 articleWe will discuss in next article
  • ASCII
  • CHAR
  • UPPER
  • LOWER
  • LTRIM
  • RTRIM
  • REVERSE
  • LEN
  • LEFT
  • RIGHT
  • CHARINDEX
  • SUBSTRING
  • REPLICATE
  • SPACE
  • PATINDEX
  • REPLACE
  • STUFF
Click here to read more

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

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON