SQL Server/T-SQL Tutorial/String Functions/LEFT

A SELECT statement that uses the LEFT function

   <source lang="sql">

4> 5> 6> create table Bankers( 7> BankerID Integer, 8> BankerName VARCHAR(20), 9> BankerContactLName VARCHAR(20), 10> BankerContactFName VARCHAR(20), 11> BankerCity VARCHAR(20), 12> BankerState VARCHAR(20), 13> BankerZipCode VARCHAR(20), 14> BankerPhone VARCHAR(20) 15> ) 16> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> SELECT BankerContactFName, BankerContactLName, 3> LEFT(BankerContactFName, 1) + 4> LEFT(BankerContactLName, 1) AS Initials 5> FROM Bankers 6> GO BankerContactFName BankerContactLName Initials

---

-------------------- --------

Smith Joe SJ Rice Red RR Cat Kit CK Black Git BG Lee Wil LW Larry Ted LT Act Yam AY Eat Hit EH Lee Sad LS Lee Bit LB (10 rows affected) 1> 2> 3> drop table Bankers; 4> GO 1> 2></source>

How to use the string functions to parse a string

   <source lang="sql">

3> 4> 5> create table Bankers( 6> BankerID Integer, 7> BankerName VARCHAR(20), 8> BankerContactLName VARCHAR(20), 9> BankerContactFName VARCHAR(20), 10> BankerCity VARCHAR(20), 11> BankerState VARCHAR(20), 12> BankerZipCode VARCHAR(20), 13> BankerPhone VARCHAR(20) 14> ) 15> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> SELECT BankerName, 3> LEFT(BankerName, CHARINDEX(" ", BankerName) - 1) AS First, 4> RIGHT(BankerName, LEN(BankerName) - CHARINDEX(" ", BankerName) ) AS Last 5> FROM Bankers 6> GO BankerName First Last

---

-------------------- --------------------

ABC Inc. ABC Inc. DEF Inc. DEF Inc. HJI Inc. HJI Inc. QWE Inc. QWE Inc. RTY Inc. RTY Inc. YUI Inc. YUI Inc. OIP Inc. OIP Inc. SAD Inc. SAD Inc. DFG Inc. DFG Inc. GHJ Inc. GHJ Inc. (10 rows affected) 1> 2> drop table Bankers; 3> GO 1></source>

LEFT(CompanyName, 1) and like

   <source lang="sql">

24> CREATE TABLE Customers ( 25> CustomerID nchar (5) NOT NULL , 26> CompanyName nvarchar (40) NOT NULL , 27> ContactName nvarchar (30) NULL , 28> ContactTitle nvarchar (30) NULL , 29> Address nvarchar (60) NULL , 30> City nvarchar (15) NULL , 31> Region nvarchar (15) NULL , 32> PostalCode nvarchar (10) NULL , 33> Country nvarchar (15) NULL , 34> Phone nvarchar (24) NULL , 35> Fax nvarchar (24) NULL 36> ) 37> GO 1> 2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111") 3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL) 4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444") 5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55") 6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777") 7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32") 8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99") 9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41") 10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745") 11> go 1> 2> 3> SELECT CustomerID, CompanyName, Country 4> FROM dbo.Customers 5> WHERE LEFT(CompanyName, 1) = N"A"; 6> GO CustomerID CompanyName Country

---

---------------------------------------- ---------------

1 A Germany 1> SELECT CustomerID, CompanyName, Country 2> FROM dbo.Customers 3> WHERE CompanyName LIKE N"A%"; 4> GO CustomerID CompanyName Country

---

---------------------------------------- ---------------

1 A Germany 1> 2> drop table Customers; 3> GO</source>

LEFT function returns a number of characters from the left-hand side of a string.

   <source lang="sql">

This can be from 1 character up to all the characters in the string. 5> 6> DECLARE @LEFT_STRING char(100) 7> SET @LEFT_STRING = "www.sqle.ru, www.sqle.ru, sqle" 8> SELECT LEFT(@LEFT_STRING,10) 9> GO

---

www.sqle (1 rows affected)</source>

select LEFT("SQL Server", 3)

   <source lang="sql">

2> 3> select LEFT("SQL Server", 3) 4> GO --- SQL (1 rows affected) 1></source>

Taking the Leftmost or Rightmost Part of a String

   <source lang="sql">

5> 6> SELECT LEFT("www.sqle.ru. sqle sqle", 10) 7> GO

---

www.sqle (1 rows affected)</source>

Use LEFT and RIGHT to get sub value from a column

   <source lang="sql">

4> 5> 6> create table Bankers( 7> BankerID Integer, 8> BankerName VARCHAR(20), 9> BankerContactLName VARCHAR(20), 10> BankerContactFName VARCHAR(20), 11> BankerCity VARCHAR(20), 12> BankerState VARCHAR(20), 13> BankerZipCode VARCHAR(20), 14> BankerPhone VARCHAR(20) 15> ) 16> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> Select BankerName, BankerContactLName + ", " + LEFT(BankerContactFName, 1) 5> + "." AS ContactName, RIGHT(BankerPhone, 8) AS Phone 6> FROM Bankers 7> WHERE SUBSTRING(BankerPhone, 2, 3) = 559 8> ORDER BY BankerName 9> GO Msg 245, Level 16, State 1, Server J\SQLEXPRESS, Line 4 Conversion failed when converting the varchar value "11-" to data type int. 1> 2> drop table Bankers; 3> GO 1> 2></source>