25.01.2007, 17:26
общий
это ответ
Здравствуйте, Драк Георгий!
Согласну Help от MS SQL Server 2000, для этого используется следующая функция:
SUBSTRING
Returns part of a character, binary, text, or image expression. For more information about the valid Microsoft® SQL Server™ data types that can be used with this function, see Data Types.
Syntax
SUBSTRING ( expression , start , length )
Arguments
expression
Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.
start
Is an integer that specifies where the substring begins.
length
Is an integer that specifies the length of the substring (the number of characters or bytes to return).
Note Because start and length specify the number of bytes when SUBSTRING is used on text data, DBCS data, such as Kanji, may result in split characters at the beginning or end of the result. This behavior is consistent with the way in which READTEXT handles DBCS. However, because of the occasional strange result, it is advisable to use ntext instead of text for DBCS characters.
Return Types
Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types.
The returned string is the same type as the given expression with the exceptions shown in the table.
Given expression Return type
text varchar
image varbinary
ntext nvarchar
Remarks
Offsets (start and length) using the ntext, char, or varchar data types must be specified in number of characters. Offsets using the text, image, binary, or varbinary data types must be specified in number of bytes.
Note Compatibility levels can affect return values. For more information about compatibility levels, see sp_dbcmptlevel.
Use SUBSTRING with text, ntext, and image data
This example shows how to return the first 200 characters from each of a text and image data column in the publishers 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:
pub_id logo pr_info
------ ---------------------- ----------
1756 0x474946383961E3002500 This is sa
(1 row(s) affected)
This example shows the effect of SUBSTRING on both text and ntext data. First, this example creates a new table in the pubs database named npr_info. Second, the example creates the pr_info column in the npr_info table from the first 80 characters of the pub_info.pr_info column and adds an ü as the first character. Lastly, an INNER JOIN retrieves all publisher identification numbers and the SUBSTRING of both the text and ntext publisher information columns.
Удачи!