Posted By

bvwatson on 05/12/15

Tagged

Versions (?)

Last Edited at 05/12/15 11:59pm

Statistics

Viewed 793 times

Favorited by 0 user(s)

Related snippets

Generate SQL query for column matching

/ Published in: Visual Basic

Written as reply to question on ittoolbox.com:"I need to execute a query in vb which can find multiple value provided by the user to find from a single column of a table."
[http://database.ittoolbox.com/groups/technical-functional/sql-l/query-to-find-multiple-values-in-single-column-5729534]

DISCLAIMERS: (1) this code is VBA/Excel, not Visual Basic; (2) this was tested in Excel; (3) the SQL was NOT tested against MS Access (the target dbms); (4) I am not a VB coder or an MS Access user -- I'm a programmer and a SQL user; (5) this code is specific to the question asked, but can be a model for similar SQL-generation problems

This code uses 3 functions:
* buildSQL -- takes user input, calls the other two functions, produces a ready-to-execute SQL SELECT statement
* buildMatchCountSQL -- takes user input, produces a select list item for inclusion in the SELECT clause
* buildMatchSQL -- takes user input, produces a predicate for inclusion in the WHERE clause

The code can be tested using the TEST() function, which prompts for user input and produces the SQL SELECT statement.

--bryan watson, May 12,2015

Highlighted TextExpand

Function buildMatchCountSQL(user_input As String) As String
  ' produce a SQL select list item of:
  '  , CASE WHEN PO.Pname LIKE '*abc*' THEN 1 ELSE 0 END
  '  + CASE WHEN PO.Pname LIKE '*def*' THEN 1 ELSE 0 END
  '  + CASE WHEN PO.Pname LIKE '*xyz*' THEN 1 ELSE 0 END
  '  AS matches
  ' Parse the user input into an array of match words
  '  replace commas with spaces so there is only 1 type of delimiter
  Dim aString As String
  aString = Replace(user_input, ",", " ")
  '  put all words in a temporary array
  Dim TempArray() As String
  TempArray() = Split(aString, " ")
  '  build SQL predicates ignoring any empty strings
  Dim intCount As Integer
  intCount = 0
  For Each Word In TempArray
    If Word <> "" Then
      ' remove leading/trailing blanks, convert word to uppercase,
      ' wrap word in quotes and wildcard characters
      Word = "'*" & Trim(UCase(Word)) & "*'"
      ' start selection with comma, join with plus sign
      If intCount = 0 Then
        buildMatchCountSQL = ", "
      Else
        buildMatchCountSQL = buildMatchCountSQL & " + "
      End If
      buildMatchCountSQL = buildMatchCountSQL & "CASE WHEN PO.Pname LIKE " & Word
      buildMatchCountSQL = buildMatchCountSQL & " THEN 1 ELSE 0 END "
      intCount = intCount + 1
    End If
  Next
  ' add the column name (unless there are no matches)
  If buildMatchCountSQL <> "" Then
    buildMatchCountSQL = buildMatchCountSQL & " AS matches"
  End If
End Function

Function buildMatchSQL(user_input As String) As String
  ' produce a SQL predicate of:
  '  AND ( PO.Pname LIKE '*abc*'
  '     OR PO.Pname LIKE '*def*'
  '     OR PO.Pname LIKE '*xyz*'
  '      )
  ' Parse the user input into an array of match words
  '  replace commas with spaces so there is only 1 type of delimiter
  Dim aString As String
  aString = Replace(user_input, ",", " ")
  '  put all words in a temporary array
  Dim TempArray() As String
  TempArray() = Split(aString, " ")
  '  build SQL predicates ignoring any empty strings
  Dim intCount As Integer
  intCount = 0
  For Each Word In TempArray
    If Word <> "" Then
      ' remove leading/trailing blanks, convert word to uppercase,
      ' wrap word in quotes and wildcard characters
      Word = "'*" & Trim(UCase(Word)) & "*'"
      ' wrap predicate list in parentheses, join with OR
      If intCount = 0 Then
        buildMatchSQL = " AND ( "
      Else
        buildMatchSQL = buildMatchSQL & " OR "
      End If
      buildMatchSQL = buildMatchSQL & "PO.Pname LIKE " & Word
      intCount = intCount + 1
    End If
  Next
  ' add the closing parenthesis (unless there are no predicates)
  If buildMatchSQL <> "" Then
    buildMatchSQL = buildMatchSQL & " )"
  End If
End Function

Function buildSQL(user_input As String) As String
  buildSQL = "SELECT PO.PCode, PO.Pname, PPLO.Price,PO.status, PPLO.Efct_Date, PO.Hlink,PO.website"
  ' add match count to the SQL select column list
  ' (if there are no words to match, this will be an empty string)
  buildSQL = buildSQL & buildMatchCountSQL(user_input)
  buildSQL = buildSQL & " FROM Pmast_Others PO"
  buildSQL = buildSQL & " INNER JOIN Product_Price_List_Others PPLO"
  buildSQL = buildSQL & " ON PO.[PCode] = PPLO.[PCode]"
  buildSQL = buildSQL & " WHERE PO.status is NULL"
  ' add conditions to the SQL predicate list
  ' (if there are no words to match, this will be an empty string)
  buildSQL = buildSQL & buildMatchSQL(user_input) 
End Function

Function TEST() as String
  Dim user_input As String
  Dim strSQL As String
  Dim newLine As String
  newLine = Chr(13)

user_input = InputBox("Enter terms separated by commas or spaces", "User Input", "")
  strSQL = buildSQL(user_input)
  Dim x As String
  If user_input = "" Then
    user_input = "(empty string)"
  End If
  x = "'" & user_input & "' => " & newLine & newLine & strSQL
  x = InputBox(x, "SQL Query String Result", x)
  TEST = strSQL
End Function

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.