SQL Server substring function usage summary

SUBSTRING

1. Syntax

SUBSTRING (expression, start, length)

2. Parameters

Expression

Is a string, binary string, text, image, column, or expression that contains a column. Do not use expressions that contain aggregate functions.

Start

The integer at the start of the substring. Start can be of the bigint type.

Length

A positive integer that specifies the number of characters or bytes of the expression to be returned. If length is negative, an error is returned. Length can be of the bigint type.

3. Remarks

The offset (start and length) of the ntext, char, or varchar data type must be specified by the number of characters ). The offset of data types such as text, image, binary, and varbinary must be specified by the number of bytes.

4. Example:

The code is as follows:	Copy code
Select substring ('gxs ',-1, 3): G Select substring ('gxs ',-1, 2): NULL Select substring ('gxs ', 0, 1): NULL Select substring ('gxs ', 1, 1): G Select substring ('gxs ', 1, 2): GX

Sample code:
The following example returns the first and last names of each employee in the Employees table:

The code is as follows:	Copy code
Select substring (First Name, 1, 1) AS Initial, Last Name FROM Employees

The following is the result set:

Initial ...... Last Name
-------------------------
A ...... Funk
M... Pearson
L ...... Calafato
N ...... Danner
J... Lee
S... Byham
M... Sutter
R... King
A...

Example
A. Use SUBSTRING on the string
The following example shows how to return only a part of the string. This query returns the last name in the authors table in one column, and the first letter in the authors table in the other column.

The code is as follows:	Copy code
USE pubs SELECT au_lname, SUBSTRING (au_fname, 1, 1) FROM authors Order by au_lname

The following is the result set:

Au_lname
-----------------------------------------
Bennet
Blotchet-Hils R
Carson C
DeFrance M
Del Castillo I
...
Yokomoto
(23 row (s) affected)
The following example shows how to display the second, third, and fourth characters in the string constant abcdef.

SELECT x = SUBSTRING ('abcdef', 2, 3)
The following is the result set:

X
----------
Bcd
(1 row (s) affected)

B. Use SUBSTRING for text, ntext, and image data
The following example shows how to return the first 200 characters from each text and image data column in the publishers table of the pubs database. Text data is returned in the form of varchar, and image data is returned in the form of varbinary.

The code is as follows:	Copy code
USE pubs SELECT pub_id, SUBSTRING (logo, 1, 10) AS logo, SUBSTRING (pr_info, 1, 10) AS pr_info FROM pub_info WHERE pub_id = '20140901'

The following is the result set:

Pub_id logo pr_info
--------------------------------------
1756 0x474946382131e3002500 This is sa
(1 row (s) affected)

The following example shows the effects of SUBSTRING on text and ntext data. First, the following example creates a new table named npr_info in the pubs database. Then, use the first 80 characters in the pub_info.pr_info column in The npr_info table to create the pr_info column and add & uuml; as the first character. Finally, inner join searches for all Publisher identification numbers and substrings in the text and ntext publisher information columns.

The code is as follows:

Copy code

If exists (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'npub _ info ') Drop table npub_info GO -- Create npub_info table in pubs database. Borrowed from instpubs. SQL. USE pubs GO Create table npub_info ( Pub_id char (4) NOT NULL REFERENCES publishers (pub_id) CONSTRAINT UPKCL_npubinfo primary key clustered, Pr_info ntext NULL ) GO -- Fill the pr_info column in npub_info with international data. RAISERROR ('Now at the inserts to pub_info... ', 0, 1) GO INSERT npub_info VALUES ('20170101', n' & uuml; This is sample text data for New Moon Books, publisher 0736 in the pubs database ') INSERT npub_info values ('20170101', n' & uuml; This is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa ') INSERT npub_info values ('20170101', n' & uuml; This is sample text data for Algodata Infosystems, publisher 1389 in the pubs da ') INSERT npub_info values ('20170101', n' & uuml; This is sample text data for Scootney Books, publisher 9952 in the pubs database ') INSERT npub_info values ('20170101', n' & uuml; This is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d ') INSERT npub_info values ('20140901', n' & uuml; This is sample text data for Ramona Publishers, publisher 1756 in the pubs datab ') INSERT npub_info values ('000000', n' & uuml; This is sample text data for GGG & G, publisher 9901 in the pubs database. GGG & G I ') INSERT npub_info values ('20170101', n' & uuml; This is sample text data for Lucerne Publishing, publisher 9999 in the pubs data ') GO -- Join between npub_info and pub_info on pub_id. SELECT pr. pub_id, SUBSTRING (pr. pr_info, 1, 35) AS pr_info, SUBSTRING (npr. pr_info, 1, 35) AS npr_info FROM pub_info pr inner join npub_info npr ON pr. pub_id = update. pub_id Order by pr. pub_id ASC