Tag Archives: SQL Server

Using Stuff to Clean Up String Concatenation

In my last post, I covered the simple, yet handy, STUFF function. It’ll take a string and “stuff it right in there”. Today, I want to cover a real work scenario where I use it quite often. I’ll use the AdventureWorks database (“AWD”) to demonstrate.

Joins create rows. If you run the following query against the AWD, you’ll see the SalesOrderId repeated for each SalesOrderDetail row:

SELECT TOP 150
	soh.SalesOrderID
	,sod.SalesOrderDetailID
FROM 
	Sales.SalesOrderHeader soh
INNER JOIN 
	Sales.SalesOrderDetail sod on sod.SalesOrderID = soh.SalesOrderID

But there are times where you want only 1 row for each SalesOrderID. And so we need a mechanism which can effectively concatenate all the SalesOrderDetailIDs for each SalesOrderID and return it as a single cell, adjacent to the corresponding SalesOrderID.

Consider the following query:

SELECT TOP 10
	soh.SalesOrderID
	,(SELECT CHAR(44) + CAST(sod.SalesOrderDetailID AS NVARCHAR(MAX))
			 FROM Sales.SalesOrderDetail sod
			 WHERE soh.SalesOrderID = sod.SalesOrderID
			 FOR XML PATH('row'), ROOT('result'),TYPE) AS SalesOrderDetailIDs
FROM Sales.SalesOrderHeader soh
ORDER BY soh.SalesOrderID

which yields the following resultset:

Figure 1


You can see that via the use of the XML query syntax and a correlated subquery, I have gone a long way to achieving our goal. All the SalesOrderDetailIDs are in a cell such that there is only 1 row for each SalesOrderID. But we are not interested in well-formed XML, having the XML type (which was done by using the TYPE directive and specifying a ROOT element). We need to peel back the XML elements and extract the value within. We can use the xQuery API to return an NVARCHAR(MAX), which has effectively returned the contents of each XML “row” element.

So now our query looks like this:

SELECT 
	soh.SalesOrderID
	,((SELECT CHAR(44) + CAST(sod.SalesOrderDetailID AS NVARCHAR(MAX))
			 FROM Sales.SalesOrderDetail sod
			 WHERE soh.SalesOrderID = sod.SalesOrderID
			 FOR XML PATH('row'),TYPE, ROOT('result')).value('/result[1]','varchar(max)')) AS SalesOrderDetailIDs
FROM Sales.SalesOrderHeader soh
ORDER BY soh.SalesOrderID

But we’re not quite there. We have 1, small, pesky comma at the beginning of the NVARCHAR, an example of which looks like this:

,1,2,3,4,5,6,7,8,9,10,11,12

This is where STUFF comes in. If we were to deal with this in isolation, it would look like this:

SELECT STUFF(',1,2,3,4,5,6,7,8,9,10,11,12', 1,1, N'')

We are telling STUFF to go to the first character, delete 1 character and replace it with an empty string. That’s how we remove the leading comma. Incorporating that into the main query, we have our final code:

SELECT 
	soh.SalesOrderID
	,(SELECT STUFF((SELECT CHAR(44) + CAST(sod.SalesOrderDetailID AS NVARCHAR(MAX))
			 FROM Sales.SalesOrderDetail sod
			 WHERE soh.SalesOrderID = sod.SalesOrderID
			 FOR XML PATH('row'),TYPE, ROOT('result')).value('/result[1]','varchar(max)'),1,1,N'')) AS SalesOrderDetailIDs
FROM Sales.SalesOrderHeader soh
ORDER BY soh.SalesOrderID

And our resultset:

Figure 2

Note: Rob Farley, in his post Handling special characters with FOR XML PATH, notes that you can:

  1. Remove the ROOT
  2. Change the xQuery to .value('.','varchar(max)')

Rob also makes the point that “it’s always felt strange to me to be returning something which I declare to be XML (using TYPE) without it being well-formed”. I agree with this. It’s a bit weird and makes it harder to see what is going on. But I wanted to point that out for completeness.

Otherwise, that’s a real world example usage of STUFF.

Stuff – The SQL Server Function

I want to dedicate a small post to STUFF. It’s a pretty handy little function in SQL Server which deletes a sequence of characters from a source string and then inserts another sequence of characters into the source string, starting at a specified position. The best way to show how it works is with examples.


Lets turn the word inimical into inimitable:

SELECT STUFF('inimical', 6, 3, 'table')

outputs: inimitable

STUFF is not 0-indexed. So, it goes to the 6th character in the string, deletes 3 characters and replaces that with the string in the last parameter. Now we have inimitable.


Add a colon into a time:

SELECT STUFF('1159', 3, 0, ':') 

outputs: 11:59


Mask a credit card number:

DECLARE @CreditCardNumber VARCHAR(20)
SET @CreditCardNumber = '9876-5432-0987-6543'

SELECT STUFF(@CreditCardNumber, 1, LEN(@CreditCardNumber) - 4, REPLICATE('X', LEN(@CreditCardNumber) - 4)) AS [Output]

outputs: XXXXXXXXXXXXXXX6543

So, STUFF is pretty cool. But there is something which I use it for quite regularly in my work. As it is quite involved, I’ll do a separate post to walk through it (this post is really just “setting the table” for that one).

Parsing a Comma Delimited List in SQL Server Scripts – Cleverly

There’s an external guy we use for really complex SQL. We have a report which has been the ruin of many a developer, and after many failures, it became apparent that developers could not do this. That’s when you need to get in a proper SQL merchant. Someone who thinks in terms of sets, rather than while loops.

But I digress. In one of his Scripts, I saw something which I found very interesting. He wrote a few short lines of code which parse a comma-delimited list (passed in as a parameter). Now, I’ve seen many a function which has been created for this, and they all seemed to fit about what I would do if I went to write it. But this guy… This is how he wrote it (inserting the result into a table variable called @HospitalIDTable):

		declare @HospitalIds CHAR (13) = '1,2,3,4,5,6,9' -- pretend this is a parameter
		declare			@XML as xml
		declare			@Delimiter as char(1) =','

		set				@XML = CAST(('<X>'+REPLACE(@HospitalIds ,@Delimiter ,'</X><X>')+'</X>') as xml)
		declare			@HospitalIDTable table (HospitalId int)
		insert into		@HospitalIDTable
		select			N.value('.', 'INT') AS HospitalId 
		from			@XML.nodes('X') AS T(N)
                -- done

If you output the @XML variable, you will see:

<X>1</X><X>2</X><X>3</X><X>4</X><X>5</X><X>6</X><X>9</X>

Now, the final SELECT statement – I don’t even understand. I know N is to make a string unicode, but value??? I need to research that. Back in a tick.

OK. So the value method is the XML data type method for XQuery. I should have known that, because I’ve done that before. Calling it off the N prefix is what threw me, and I still haven’t totally grokked what is going on there. But it is as cool as heck and if you do a SELECT on the @HospitalIDTable table variable, you get your list as a column of data.

Very concise and cool.

Experts like this guy make light work of complex SQL. We’ve had several dev’s look at his scripts and everyone agrees that their relationship with SQL is not on the same level as this guys. We’re front-end/middle-tiers devs. What did you expect?!

Hitting Host Machine’s SQL Server from within a VM

I had a situation at home where I was running some code in Visual Studio on a virtual machine. The virtual machine was running using VMWare Player on my desktop machine.

I needed to access the database which was running on my host machine (the desktop machine) from within the VM. So I had to come up with a Connection String which was able to target the host machine. The following Connection String achieves that:

<add name="DefaultConnection" connectionString="Data Source=192.168.25.1,2310;Network Library=DBMSSOCN;Initial Catalog=AdventureWorld;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Note, I had to open port 2310 on the host machine’s firewall.

Now, I don’t need to install Sql Server on my virtual machines (which always seems to be a pain in the proverbial).

LocalDb – Where the Dbs Live and Some Simple Facts

I always forget where to find the mdf files which are created by localdb (hence this post).
If we take a look at the connection string, it’s not too instructional as to the location:

connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=ContosoUniversity2;Integrated Security=SSPI;"

In this post, I’ll identify the locations where you are likely to find that elusive localdb database.

Local AppData
Where you haven’t created the database yourself (perhaps you downloaded a project which creates and seeds the database via EF Code First), it can usually be found in this directory:
%AppData%\..\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0. Note the two dots before the string, Local, in that path. Without that, you’ll be dumped into the Roaming directory, which is not the right place.

User Profile
If you don’t find it there, another place it is often found is in the User Profile – %USERPROFILE%. When you create a new database in Sql Server, either through the GUI or by running the following Sql, the default path is in the %USERPROFILE% location:

create database SpiderMonkey

SQL Server Management Studio
Ultimately, you can dictate where the localdb mdf/ldf files for a database are created. Either through the GUI in SQL Server Management Studio, or by running the following into SQL Server Management Studio’s query window:

create database SpiderMonkey on (name='SpiderMonkey', filename='c:\DBs\SpiderMonkey.mdf')

Visual Studio
If you create a localdb database using Visual Studio, the default location is: %USERPROFILE%\AppData\Local\Microsoft\VisualStudio\SSDT
The following 2 pictures outline the workflow of using Visual Studio to create a localdb database. As you can see from the 2nd picture, you can choose where the mdf files are created:
CreateLocalDb4

CreateLocalDb3

As a final comment, the whole idea of localdb is that we developers should not need to be concerning ourselves with issues about the database. The team who created localdb did so with the intention of abstracting away the database so that we could focus on development. But it is still nice to know where to find these things, even if just to clean up your hard drive!