March 8, 2016 by Kenneth Fisher
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.
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.