$Path = "C:\temp\"
"$($Path)RunThisScript_$(get-date -f yyyyMMdd_hhmmss).sql"

Basically a subexpression looks like this $(). Whatever is in the parenthesis gets put into the string. I’m doing it twice here. I’m starting with RunThisScript.sql and first I add in my path variable, the next adds a formatted date/time string with the end result of:

C:\temp\RunThisScript_20211005_112552.sql

I have to admit, I really like this. It feels a lot cleaner than what I’d have to do in SQL Server.

DECLARE @Path varchar(50) = 'C:\Temp\'
PRINT @Path + 'RunThisScript'+format(getdate(),'yyyyMMdd_hhmmss')+'.sql'

Every now and again I see someone get lazy and declare a string (CHAR, NCHAR, VARCHAR and NVARCHAR) without specifically declaring what the length is going to be. This can lead to some interesting problems. First of all it’s usually going to be a length of one.

-- Parameters
CREATE PROCEDURE #sp_Test 
	@Param1 varchar
	AS
BEGIN
	PRINT @Param1;
END
SELECT * FROM tempdb.sys.parameters WHERE name = '@Param1';
-- 1
----------------------------------------------------
-- Variables
DECLARE @Var1 VARCHAR = REPLICATE('a',8000);
SELECT DATALENGTH(@Var1);
-- 1
----------------------------------------------------
-- Columns
CREATE TABLE #Temp (Col1 VARCHAR);
EXEC tempdb..sp_help '#Temp';
-- 1

 
Which is a pretty unusual need. That said it does happen. The first problem though, is that most people don’t realize what they are going to end up with and only getting one character can be a nasty surprise.

DECLARE @Var1 VARCHAR = 'Surprise';
PRINT @Var1;
-- S

 
But let’s say you do know, and it is what you intended. There is a pretty good chance the person after you won’t realize what’s going on. Not many people do after all. This is going to cause a serious maintenance issue. Any code that someone after you can’t understand is going to cause a maintenance issue. In those circumstances you usually just add a comment or two. Of course at that point it’s faster and easier to just specify the length.

I should also point out that it’s not always going to be a length of one. You can figure out when and how long the string is going to be on your own though. Just to help drive home that you should always define the string length. It’s just easier.

Not everyone has that nightmare? Just me? Really? Um .. well .. You know, let’s just forget I said anything.

Escaping strings is an important concept when working with computers. Not just DBAs, database developers, or even application developers. If you work with strings then this is important for you.

Here’s the thing. Computers are amazing tools, but they are very literal. A computer does exactly what it’s told. That means that if you tell the computer that you’re going to put single quotes (‘) on either side of a string then the computer is going to take that first single quote and think begining a string and the very next single quote it’s going to think end of the string, even if that’s not what you intended.

‘Let’s take a look’

So what can we do to help the computer out? It really depends on the language. In T-SQL we double up on the character.

‘Let”s take a look’

In C# it’s a backslash.

‘Let\’s take a look’

In HTML we have to convert certain characters into special codes. (Which I’m not going to demonstrate since this is HTML and it would be a pain in the neck.)

So why is this so important? Setting variables is a good example.

DECLARE @LastName varchar(50) = 'O''Conner'

Again, this is important regardless of language and, honestly, regardless of job. If you work with strings you need to not only understand this concept, but you need to know how the particular language you are working with handles escaping.

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.

-- This is the best evidence I could find of the 
-- data type of a string
SELECT SQL_VARIANT_PROPERTY('A string','BaseType');
--Returns: varchar

Note that it is varchar and not varchar(max). Varchar and varchar(max) have very different size limits. A varchar(max) has a limit of 2GB and a varchar has a limit of 8000 characters. So what is this mistake I’m talking about? Watch.

DECLARE @str varchar(max);

SET @str = REPLICATE('1',950) +
	REPLICATE('2',950) +
	REPLICATE('3',950) +
	REPLICATE('4',950) +
	REPLICATE('5',950) +
	REPLICATE('6',950) +
	REPLICATE('7',950) +
	REPLICATE('8',950) +
	REPLICATE('9',950) +
	REPLICATE('0',950); 

SELECT LEN(@str);
GO
-- Output 8000

And of course 10 * 950 characters is 9500. This is a rather contrived example but again if you are dealing with long pieces of dynamic SQL it can and does come up occasionally. So what’s the fix? Add smaller strings multiple times like this.

DECLARE @str varchar(max);

SET @str = REPLICATE('1',950) +
	REPLICATE('2',950) +
	REPLICATE('3',950) +
	REPLICATE('4',950) +
	REPLICATE('5',950); 
SET @str = @str + 
	REPLICATE('6',950) +
	REPLICATE('7',950) +
	REPLICATE('8',950) +
	REPLICATE('9',950) +
	REPLICATE('0',950); 

SELECT LEN(@str);
-- Output 9500

Personally I try to break up strings long before I run into issues, it’s safer that way. I still mess up occasionally though, and when I get a weird error that looks like my string has been truncated this is one of the first things I check for.