SQL Server substring function usage summary

Source: Internet
Author: User
Tags sql server substring

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.