SQL Server Helper - Tip of the Day

The LEFT string function returns the left part of a character string with the specified number of characters. The syntax of the LEFT string function is as follows:

LEFT ( <character expression>, <integer expression> )

The first parameter of the LEFT string function is an expression of character or binary data. It can be of any data type, except TEXT or NTEXT, that can be implicitly converted to VARCHAR or NVARCHAR. The second parameter of the LEFT string function is a positive integer expression that specifies how many characters of the to extract and return.

Here are a few examples on the uses of the LEFT string function:

Usage #1 : Extract First Name from a Full Name

DECLARE @FullName VARCHAR(100) = 'Mickey Mouse'
SELECT LEFT(@FullName, CHARINDEX(' ', @FullName) - 1) AS [FirstName]

FirstName
-----------
Mickey

Usage #2 : Extract the Local Name or the User Name of the Recipient in an Email Address

DECLARE @EmailAddress VARCHAR(100) = 'sqlserverhelper@sql-server-helper.com'
SELECT LEFT(@EmailAddress, CHARINDEX('@', @EmailAddress) - 1) AS [UserName]

UserName
-------------------
sqlserverhelper

Usage #3 : Extract the Domain Name from a URL

DECLARE @URL VARCHAR(100) = 'http://www.sql-server-helper.com/default.aspx'
SELECT LEFT(REPLACE(@URL, 'http://', ''), 
       CHARINDEX('/', REPLACE(@URL, 'http://', '')) - 1) AS [DomainName]

DomainName
---------------------------
www.sql-server-helper.com

Usage #4 : Extract the Area Code from a Phone Number

DECLARE @PhoneNumber VARCHAR(20) = '(555)987-6543'
SELECT LEFT(REPLACE(@PhoneNumber, '(', ''), 3) AS [AreaCode]

AreaCode
---------
555

Usage #5 : Left and Right Justify a String

DECLARE @Contents TABLE ( 
    [Title]         VARCHAR(50),
    [PageNumber]    INT
)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Introduction', 1)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Table of Contents', 2)

INSERT INTO @Contents ( [Title], [PageNumber] )
VALUES ( 'Index', 100)

SELECT LEFT([Title] + REPLICATE('.', 100), 100) + 
       RIGHT('     ' + CAST([PageNumber] AS VARCHAR(5)), 5)
FROM @Contents

Introduction......................................    1
Table of Contents.................................    2
Index.............................................  100