This article is specific to MS SQL Server. There can be various need based scenarios where the data returned by the SELECT statement should be limited to certain length for the columns of data type VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, and IMAGE data types. This can be achieved using SUBSTRING or LEFT functions for specific columns in SELECT statement. But if you have a SELECT statement having many columns of the data types specified before and you want to apply data returned length limitation for each column, the better option would be to use TEXTSIZE command.
TEXTSIZE command in SQL Server can be used to limit the length of data returned for each column in a SELECT statement. This command is applicable to the columns of following data types:
- VARCHAR(MAX)
- NVARCHAR(MAX)
- VARBINARY(MAX)
- TEXT
- NTEXT
- IMAGE
The syntax to use the command is as given below:
SET TEXTSIZE < Specify the number of bytes here > Example: The below statement will set the data returned length to 200 characters and run the SELECT command with the data returned length to 200 characters for each column
SET TEXTSIZE 200
SELECT RecID, TxtCol01, TxtCol02, TxtCol03 from MyTableNameTo fetch the default size of TEXTSIZE, use the below command
SELECT @@TEXTSIZETo set the TEXTSIZE value to its default value of
2147483647 bytes, use the below command
SET TEXTSIZE 2147483647