SQL Server/T-SQL Tutorial/String Functions/SUBSTRING
Содержание
- 1 A SELECT statement that uses the LEFT, RIGHT, and SUBSTRING functions
- 2 Get the last name
- 3 Return first name
- 4 Returning a Chunk of an Expression
- 5 select SUBSTRING("(1212", 7, 8)
- 6 SUBSTRING(FullName, CHARINDEX(" ", FullName) + 1, LEN(FullName))
- 7 SUBSTRING is used to retrieve part of a string from another string. The syntax for the function is as follows:
- 8 Substring with variable
A SELECT statement that uses the LEFT, RIGHT, and SUBSTRING functions
<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>
Get the last name
<source lang="sql">
3> DECLARE @FullName VarChar(25) 4> SET @FullName = "George Washington" 5> SELECT SUBSTRING(@FullName, CHARINDEX(" ", @FullName) + 1, LEN(@FullName)) 6> GO
Washington (1 rows affected)</source>
Return first name
<source lang="sql">
3> DECLARE @FullName VarChar(25) 4> SET @FullName = "George Washington" 5> -- Return first name: 6> SELECT SUBSTRING(@FullName, 1, CHARINDEX(" ", @FullName) - 1) 7> -- ^String ^Start ^Returns space index ^Don"t include space 8> GO
George (1 rows affected)</source>
Returning a Chunk of an Expression
<source lang="sql">
4> 5> DECLARE @BankAccountNumber char(14) 6> SET @BankAccountNumber = "1111-1111-1111" 7> SELECT "XXXX-" + SUBSTRING(@BankAccountNumber, 6,4) + "-XXXX" Masked_BankAccountNumber 8> GO Masked_BankAccountNumber
XXXX-1111-XXXX (1 rows affected) 1></source>
select SUBSTRING("(1212", 7, 8)
<source lang="sql">
2> select SUBSTRING("(559) 555-1212", 7, 8) 3> GO
555-1212 (1 rows affected)</source>
SUBSTRING(FullName, CHARINDEX(" ", FullName) + 1, LEN(FullName))
<source lang="sql">
3> CREATE TABLE #MyNames (FullName VarChar(50)) 4> GO 1> INSERT INTO #MyNames (FullName) SELECT "Fred 2~ INSERT INTO #MyNames (FullName) SELECT "Wilma 3> INSERT INTO #MyNames (FullName) SELECT "Barney 4~ INSERT INTO #MyNames (FullName) SELECT "Betty 5> INSERT INTO #MyNames (FullName) SELECT "George 6~ INSERT INTO #MyNames (FullName) SELECT "Jane 7> 8> SELECT 9> SUBSTRING(FullName, 1, CHARINDEX(" ", FullName) - 1) AS FirstName 10> , SUBSTRING(FullName, CHARINDEX(" ", FullName) + 1, LEN(FullName)) AS LastName 11> FROM #MyNames 12> GO (1 rows affected) (1 rows affected) (1 rows affected) FirstName LastName
--------------------------------------------------
Fred INSERT INTO #MyNames (FullName) SELECT Barney INSERT INTO #MyNames (FullName) SELECT George INSERT INTO #MyNames (FullName) SELECT (3 rows affected) 1> 2> 3> drop table #MyNames; 4> GO 1></source>
SUBSTRING is used to retrieve part of a string from another string. The syntax for the function is as follows:
<source lang="sql">
SUBSTRING(string_to_remove_string_from, start_position, length) 6> SELECT SUBSTRING("sqle sqle",2,3) 7> GO
ava (1 rows affected)</source>
Substring with variable
<source lang="sql">
4> DECLARE @FullName VarChar(25) 5> SET @FullName = "George Washington" 6> SELECT SUBSTRING(@FullName, 4, 6) 7> GO
rge Wa (1 rows affected)</source>
