SQL Server/T-SQL Tutorial/String Functions/LEFT
Содержание
- 1 A SELECT statement that uses the LEFT function
- 2 How to use the string functions to parse a string
- 3 LEFT(CompanyName, 1) and like
- 4 LEFT function returns a number of characters from the left-hand side of a string.
- 5 select LEFT("SQL Server", 3)
- 6 Taking the Leftmost or Rightmost Part of a String
- 7 Use LEFT and RIGHT to get sub value from a column
A SELECT statement that uses the LEFT function
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>
How to use the string functions to parse a string
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>
LEFT(CompanyName, 1) and like
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
LEFT function returns a number of characters from the left-hand side of a string.
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)
select LEFT("SQL Server", 3)
2>
3> select LEFT("SQL Server", 3)
4> GO
---
SQL
(1 rows affected)
1>
Taking the Leftmost or Rightmost Part of a String
5>
6> SELECT LEFT("www.sqle.ru. sqle sqle", 10)
7> GO
--------------------
www.sqle
(1 rows affected)
Use LEFT and RIGHT to get sub value from a column
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>