SUBSTRING (Transact-SQL)

Aşağıdaki örnek, bir karakter dizesini yalnızca bir bölümünü almayı gösterir.Gelen Contact Tablo, bu sorguda soyadı ilk ilk ikinci sütundaki yalnızca bir sütun verir.

USE AdventureWorks;
GO
SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial
FROM Person.Contact
WHERE LastName like 'Barl%'
ORDER BY LastName;

Here is the result set.

Soyadı ilk

--------------------------------- -------

Barley R

Barlow B

(2 satırları etkilenen)

Işte, ikinci, görüntüleme, dize sabit, üçüncü ve dördüncü karakterleridir abcdef.

SELECT x = SUBSTRING('abcdef', 2, 3);

Here is the result set.

x

----------

BCD

(1 satırları etkilenen)

The following example shows how to return the first 10 characters from each of a text and image data column in the pub_info table of the pubs database.text data is returned as varchar, and image data is returned as varbinary.

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 = '1756';

Here is the result set.

Amblem pr_info pub_id

------ ---------------------- ----------

1756 0x474946383961E3002500 sa budur.

(1 satırları etkilenen)

Aşağıdaki örnek üzerinde her ikisi de SUBSTRING etkisini gösterir text ve ntext Veri. Ilk olarak, bu örnek, yeni bir tablo oluşturur pubs Veritabanı adı npub_info. Ikinci örnek oluşturur pr_info sütunda npub_info ilk 80 karakter tablosundan pub_info.pr_info sütun ve ekler bir ü ilk karakter. Son olarak, bir INNER JOIN Tüm yayımcı kimlik numaraları alır ve SUBSTRING Her iki text ve ntext Publisher, bilgileri sütunlar.

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('0736', N'üThis is sample text data for New Moon Books, publisher 0736 in the pubs database');
INSERT npub_info values('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa');
INSERT npub_info values('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da');
INSERT npub_info values('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database');
INSERT npub_info values('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d');
INSERT npub_info values('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab');
INSERT npub_info values('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i');
INSERT npub_info values('9999', N'ü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 = npr.pub_id
ORDER BY pr.pub_id ASC;