Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Query String Parser Function
SQL Server Helper
Home > > Tip of the Day
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

Back to Tip of the Day List Next Tip