SQL Server Management Studio (SSMS) is a great tool for writing and running queries. This post will provide some extra tips on querying text fields in a database table. These include:
  • Query fields with long or formatted text
  • Copying strings by dragging them from the results grid
  • T-SQL query tips for working with strings

Long Text Fields

In this example, I have a database for documents. In my Documents table, I have a large portion of the United States Declaration of Independence. Here is a screenshot of a query that returns my document text.
Text Query
In the Results pane, the DocumentText column holds all of the text, but you have to expand the column and scroll to see it all. Also, there are line breaks in my text that don’t show up in this grid view. One solution is to change the output to “Results to Text” instead of the default “Results to Grid”. You can do this through the Query menu or by using the toolbar button. After selecting this mode, re-run the query.
Results to Text
Now the document text shows us the line breaks and allows us to more easily select and search it. But the text is cut off after 256 characters. This is a setting that you can change:
  1. On the Tools menu, select Options.
  2. In the Options dialog, expand Query Results.
  3. Expand SQL Server, and then select Results to Text.
  4. Change the Maximum number of characters displayed in each column to 8192 (the maximum).
Options Dialog
Press OK to save this change. But if you run the query again, you won’t see any change. You must close the current query window and open a new one to see the results!
Text Query Results with More Text
Of course, you’re still limited to the maximum of 8192 characters, but in all other cases, this can be very helpful if you’re wanting to examine text with tabs and line breaks.

Dragging Text From Results

My next tip is that you can drag text out of the results window (grid view) into the query window. For example, let’s say that I ran a query on all of my documents and saw that one was named “The Declaration of Independence”. I could create a WHERE clause and type that in. I could also right-click on the cell in the grid view and copy the text. But an even faster solution is to click and drag the text from the cell onto the query window. This works from other places in SSMS too, such as object explorer.

T-SQL Text Tips

Finally, there are many functions that help you query text fields. For a full list, see the MSDN topic called String Functions (Transact-SQL). For example, I could find out how many characters are in my document with the LEN function:
SELECT LEN(DocumentText) as DocumentLength
FROM Documents
WHERE DocumentName = 'The Declaration of Independence'
In the WHERE clause, you can also do pattern matching with the LIKE keyword and the % wildcard character. For example, the following query returns all documents that have the word “Declaration” in the title:
SELECT *
FROM Documents
WHERE DocumentName LIKE '%Declaration%'
You can concatenate strings using the plus sign. The following example concatenates the DocumentName and DocumentText columns and calls the derived column DocumentNameAndText.
SELECT DocumentName + ' : ' + DocumentText as DocumentNameAndText
FROM Documents
My final tip is that you can cast non-text fields to text. The following example casts the Id field, which is an int, to nvarchar(10) and then concatenates it to the other columns.
SELECT CAST(Id as nvarchar(max)) + ' : ' + DocumentName + ' : ' + DocumentText as DocumentAllColumns
FROM Documents