Sql server® 2012 t-sql fundamentals

The 

LEFT

 and 

RIGHT

 Functions

The LEFT and RIGHT functions are abbreviations of the SUBSTRING function, returning a requested 

number of characters from the left or right end of the input string.

www.it-ebooks.info

---

 

CHAPTER 2

 

Single-Table Queries

 

 67

Syntax

LEFT(string, n), RIGHT(string, n)

The first argument, string, is the string the function operates on. The second argument, n, is the 

number of characters to extract from the left or right end of the string. For example, the following 

code returns the output ‘cde’.

SELECT RIGHT('abcde', 3);

The 

LEN

 and 

DATALENGTH

 Functions

The LEN function returns the number of characters in the input string.

Syntax

LEN(string)

Note that this function returns the number of characters in the input string and not necessarily 

the number of bytes. With regular characters, both numbers are the same because each character re-

quires one byte of storage. With Unicode characters, each character requires two bytes of storage (in 

most cases, at least); therefore, the number of characters is half the number of bytes. To get the num-

ber of bytes, use the DATALENGTH function instead of LEN. For example, the following code returns 5.

SELECT LEN(N'abcde');

The following code returns 10.

SELECT DATALENGTH(N'abcde');

Another difference between LEN and DATALENGTH is that the former excludes trailing blanks but 

the latter doesn’t.