Excel SQLConcat Function

[Update 02/14/14]  

The code contained in this article contains a flaw. I have corrected the issue and explained it here. The new version of the function contains some extra functionality as well, so I encourage you to check it out. 

I do a lot of adhoc querying of SQL Server databases. Often, I find myself given a list of items to base the query on. While it’s simple enough to write a quick

SELECT foo

FROM bar

WHERE foo IN (1,3,27,52)

It quickly becomes down right arduous to type out that IN statement when it needs to be quoted and there are 20 items in the list you’re given. I’m going to show you how to create an custom Excel function that pivots that list into something we can use to query with.

1:  Function SQLConcat(rng As Range, Optional quoted As Boolean) As String  
2:  ' ***************************************************************  
3:  ' * Returns a comma separated list for use in SQL IN statements *  
4:  ' * Params                                                      *  
5:  ' * - rng: Range of cells to concatenate                        *  
6:  ' * - quoted: True/False. If true, values are placed inside     *  
7:  ' * of single quotes. Default of false                          *  
8:  ' * Author: Christopher J. McClellan                            *  
9:  ' *Published under Creative Commons Attribution-Share Alike     *   
10:  '*   http://creativecommons.org/licenses/by-sa/3.0/            *  
11:  '* You are free to change, distribute, and pretty much do      *  
12:  '* whatever you like with the code, but you must give credit   *  
13:  '* to the original author and publish any derivitive of this   *  
14:  '* code under the same license.                                *  
15:  ' ************************************************************** 
16:  Dim tmp As String 'temporary string  
17:  Dim row As Long  'first cell is special case  
18:  row = 0      'initalize row count  
19:  Dim c As Object  'cell  
20:  If IsMissing(quoted) Then  
21:    quoted = False  
22:  End If  
23:  If quoted Then  
24:    For Each c In rng.Cells  
25:      If row = 0 Then  
26:        tmp = "'" & c.Value & "'"  
27:      Else  
28:        tmp = tmp & ",'" & c.Value & "'"  
29:      End If  
30:      row = row + 1  
31:    Next c  
32:    'return  
33:    SQLConcat = tmp  
34:  Else  
35:    For Each c In rng.Cells  
36:      If row = 0 Then  
37:        tmp = c.Value  
38:      Else  
39:        tmp = tmp & "," & c.Value  
40:      End If  
41:      row = row + 1  
42:    Next c  
43:    'return  
44:    SQLConcat = tmp  
45:  End If  
46:  End Function  

Now all you need to do is save it to a custom add-in file and then you can call it from inside of Excel.

User Defined Function Dialog

User Defined Function Dialog

Screen shot of Excel using udf SQLConcat

Using SQLConcat

One last thing. I’d like to give a shout out to CodeFormatter for helping me display the vba correctly. Hopefully I’ve saved you as much time as they saved me.

Advertisements
  1. #1 by Majd Al-Jeroudi on October 23, 2013 - 8:43 am

    thanks bro 😀

    Like

  1. Excel SQLConcat Function Revisited | Christopher J. McClellan

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: