Ever wonder what that goofy query in the header means?


March 8, 2016 by Kenneth Fisher

T-SQL Tuesday 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.

Last but not least we construct the final string.

		UPPER(LEFT(display_term,1)) +
		SUBSTRING(display_term,2,20) +

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.

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

  1. Nico says:

    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.

  2. […] Kenneth Fisher explains his blog header, which shows different ways to manipulate text in SQL Server: […]

  3. […] was Kenneth Fisher (@sqlstudent144). He walks us through the query in the header of his blog, which does a bit of text manipulation to create a hidden subtitle. I’ll admit I’ve […]

  4. […] be fun for everyone. There are two clues.  Everything listed below can be found in some way in the header of my blog. Beyond that, each entry contains all of the letters for that answer with a few extra letters […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: