Ok, here’s the situation. You need to format an array of values for use in a SQL query. You want the final output to look something like this:
SELECT * from dbo.T1 where col1 IN (1, 15, 17, 23, 55, 67, 88)
The elements in the IN clause are what you got from the array. It doesn’t matter how you got the elements into the array, just that you did. However, for the purposes of this, we’ll just fill the array ourselves.
$a = 1, 15, 17, 23, 55, 67, 88
$a
So now that we’ve got the elements in the array. For me this is typically the result of a query. I’m usually doing something like this:
$a = invoke-sqlcmd -query "select ID from SomeTable where col1 = 'current'"
$a
That’s how I typically fill that array. So anyway, this method works really well as long as the values in the array are numbers. See, in the SQL statement they don’t have to be surrounded by quotes so you can just join them.
Here’s how you join array elements into a single string in PS.
$a = 1, 15, 17, 23, 55, 67, 88
$b = [string]::Join(", ", $a)
$b
$Query = "SELECT * from dbo.T1 where col1 IN ($b)"
The JOIN method takes 2 arguments: The first is the element you want to place between each array element, in my case a comma followed by a space, and the array to apply it to.
Like I said above though this doesn’t really help when the elements need to be quoted.
What I need this time is for the SQL query to look like this:
SELECT * from dbo.T1 where col1 IN (‘2/2/2103’, ‘2/3/2013’, ‘2/4/2013’, ‘2/5/2013’)
The problem is that the elements aren’t quoted when they come in. If you wanted you could add the quotes to the query as you get it back from SQL, but there’s no guarantee you have control over that. And let’s be honest, it’s just fun to do it in PS.
Here’s how to use the same JOIN method in PS to surround the array elements in quotes.
$a = '2/2/2103', '2/3/2013', '2/4/2013', '2/5/2013'
$b = [string]::Join("', '", $a)
$b
$Query = "SELECT * from dbo.T1 where col1 IN ('$b')"
Actually, the differences here are subtle. What’s changed is in the Join method, instead of placing a comma between the elements, I’m placing that comma in single quotes. Here’s the result what you’ll get right after you do the JOIN.
2/2/2103′, ‘2/3/2013’, ‘2/4/2013’, ‘2/5/2013
Notice it gave you the quotes everywhere except the outer boundaries. And we took care of that by placing single quotes around it in the $Query line.
Alright, that’s it. You can now surround array elements in quotes if you need to so you can format the array for use in a SQL query.
Good luck.
Sean, great post, but isn’t it more advisable to format dates in ISO YYYY-MM-DD format? I have to deal with machine in London as well as the US, and UK machines parse a date as DD/MM/YYYY, so thats not an academic problem for me.
On another note, is there any way with SqlCmd to use ADO.NET sql parameters? Thats one of the many reason’s I use Chad Millers Invoke-SqlCmd2
Hey Justin, it doesn’t matter how you format the dates because this method counts for all strings, not just dates. I just chose dates as an example.
Also, you can’t really use params like you can in ADO.NET, but you can use vars and pass them into the command so it’s close.
I don’t like using any 3rd party tools because I won’t have them everywhere I go and I don’t want to rely on them. I’ll always have PS, but I may not be able to install anything on top of it.
Justin, I’ll use Invoke-SQLCMD when the query is small, but ADO.NET directly when it’s a more complicated query or stored procedure. I don’t use SQLPSX for the same reasons as Sean, and ADO.NET is pretty easy to learn.
hi,
great tip here.
Is the join method (.NET ?) you are using the same as the powershell join method ? ($a -join vs [string]::join)
Sean – Saw you at SQL PASS great presentation. Just saved me here as well much appreciated.