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.