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

A SELECT statement that uses the LEFT, RIGHT, and SUBSTRING functions

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>

Get the last name

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)

Return first name

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)

Returning a Chunk of an Expression

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>

select SUBSTRING("(1212", 7, 8)

2> select SUBSTRING("(559) 555-1212", 7, 8)
3> GO
--------
555-1212
(1 rows affected)

SUBSTRING(FullName, CHARINDEX(" ", FullName) + 1, LEN(FullName))

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>

SUBSTRING is used to retrieve part of a string from another string. The syntax for the function is as follows:

SUBSTRING(string_to_remove_string_from, start_position, length)
6> SELECT SUBSTRING("sqle sqle",2,3)
7> GO
------
ava
(1 rows affected)

Substring with variable

4> DECLARE @FullName VarChar(25)
5> SET @FullName = "George Washington"
6> SELECT SUBSTRING(@FullName, 4, 6)
7> GO
------
rge Wa
(1 rows affected)