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.

SELECT LEFT(STUFF(@@VERSION,2,8,'y'),14) + 
		UPPER(LEFT(display_term,1)) +
		SUBSTRING(display_term,2,20) +
		CHAR(46)

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.