It’s T-SQL Tuesday time again (Number 76!) and this month we are hosted by Bob Pusateri (b/t) who has invited us to talk about Text. Text. What an interesting subject. So much of our jobs is all about searching for and manipulating text.

When I started this blog a friend of mine suggested I write a really complicated query as a header. Now I’m not sure how complicated it really is, but I find it fairly amusing, and the whole point of it is to manipulate some text to generate a different set of text. So this seems like a good time to go through it and explain what’s going on.

Jumping right in with the CTE (Common Table Expression) at the top

WITH ToLearn AS (
SELECT row_number() OVER (ORDER BY keyword) AS RowNumber, *
FROM sys.dm_fts_parser('FORMSOF(INFLECTIONAL , ''study'')',1033,0,0)
)

There is actually quite a bit going on in this part. First we are pulling data from the system function sys.dm_fts_parser. This function is the equivelent of running a full text query on a string. In this particular case we are using the FORMSOF predicate with the INFLECTIONAL type to pull the different forms of the word study. The row_number() function is to order the results and let us pull a specific entry.

Next let’s look at the bottom of the query.

FROM ToLearn
WHERE RowNumber = 2

This bit’s pretty simple. We are querying from the CTE and only want to pull the second row (as ordered by the ROW_NUMBER above). In this case it pulls the row containing the word studies.

The last line is again pretty simple. CHAR just returns the character code for an ASCII value. And 46 translates to a period.

The two lines in the middle use LEFT, SUBSTRING and UPPER to put the display_term in proper case. (First letter upper case.)

And last but not least we use my favorite text function STUFF to remove a piece (eight characters long, starting at the second character) of the contents of the @@VERSION system variable (which contains, you guessed it, the version of SQL Server that’s being run) and replace it with a y. Then we use the LEFT function to pull the first 14 characters of what’s left. (<- See what I did there?)

So has anyone figured out what the result is?

My SQL Server Studies.

This query was really just me messing around and I have to admit part of has wondered if anyone ever bothered to try to figure what it produces. Either way, now you know. And if nothing else it’s a nice cross section of various ways T-SQL can be used to manipulate text.

4 thoughts on “Ever wonder what that goofy query in the header means?”

Hi Kenneth, once again thanks for your always concise and interesting articles. To answer your question, a number of years ago I was curious and worked out the header at the time. Keep up your great work.