LEFT and RIGHT

LEFT & RIGHT

LEFT and RIGHT are used to retrieve parts of a content in a column starting from left and right respectively.

CREATE TABLE #temp
(
ID INT,
Name VARCHAR(10)
);

INSERT INTO #temp
VALUES
(123, 'John'), (23456,'Sarah'), (345678,'Tim');

SELECT * FROM #temp

CREATE TABLE #temp

(

ID INT,

Name VARCHAR(10)

);

INSERT INTO #temp

VALUES

(123, 'John'), (23456,'Sarah'), (345678,'Tim');

SELECT * FROM #temp

ID	Name
123	John
23456	Sarah
345678	Tim

ID Name

123 John

23456 Sarah

345678 Tim

John has four characters. Starting from left for 2 characters, we should get ‘Jo.’ Starting from right for 2 characters, we should get ‘hn.’

SELECT LEFT(Name, 2) AS Left_Name,
       RIGHT(Name, 2) AS Right_Name
FROM #temp

SELECT LEFT(Name, 2) AS Left_Name,

RIGHT(Name, 2) AS Right_Name

FROM #temp

Left_Name	Right_Name
Jo	        hn
Sa	        ah
Ti	        im

Left_Name Right_Name

Jo hn

Sa ah

Ti im

In addition to text, LEFT and RIGHT can also retrieve numbers from integer variable.

SELECT LEFT(Name, 2) AS Left_Name,
          RIGHT(Name, 2) AS Right_Name,
          LEFT(ID, 2) AS Left_ID,
          RIGHT(ID, 2) AS Right_ID
FROM #temp

SELECT LEFT(Name, 2) AS Left_Name,

RIGHT(Name, 2) AS Right_Name,

LEFT(ID, 2) AS Left_ID,

RIGHT(ID, 2) AS Right_ID

FROM #temp

Left_Name	Right_Name	Left_ID	Right_ID
Jo	        hn	        12	23
Sa	        ah	        23	56
Ti	        im	        34	78

Left_Name Right_Name Left_ID Right_ID

Jo hn 12 23

Sa ah 23 56

Ti im 34 78

However, the returned data type will be character.

SELECT SUM(temp2.LEFT_ID)
FROM
(
SELECT LEFT(Name, 2) AS Left_Name,
          RIGHT(Name, 2) AS Right_Name,
          LEFT(ID, 2) AS Left_ID,
          RIGHT(ID, 2) AS Right_ID
FROM #temp ) AS temp2

SELECT SUM(temp2.LEFT_ID)

FROM

(

SELECT LEFT(Name, 2) AS Left_Name,

RIGHT(Name, 2) AS Right_Name,

LEFT(ID, 2) AS Left_ID,

RIGHT(ID, 2) AS Right_ID

FROM #temp ) AS temp2

Operand data type varchar is invalid for sum operator.

1	Operand data type varchar is invalid for sum operator.

Unless, we use CAST .

SELECT SUM(CAST(temp2.LEFT_ID AS INT)) AS SUM
FROM
(
SELECT LEFT(Name, 2) AS Left_Name,
	   RIGHT(Name, 2) AS Right_Name,
	   LEFT(ID, 2) AS Left_ID,
	   RIGHT(ID, 2) AS Right_ID
FROM #temp ) AS temp2

SELECT SUM(CAST(temp2.LEFT_ID AS INT)) AS SUM

FROM

(

SELECT LEFT(Name, 2) AS Left_Name,

RIGHT(Name, 2) AS Right_Name,

LEFT(ID, 2) AS Left_ID,

RIGHT(ID, 2) AS Right_ID

FROM #temp ) AS temp2

SUM
69

SUM