Function String Extras Class

CharactersToSQLInExpression

Convert a string like ABC into a SQL WHERE IN expression. Each unique character becomes a string within the IN expression.

Signature (Visual Basic / VBA)

Public Function CharactersToSQLInExpression( _
      ByVal vChars As Variant _
    , Optional ByVal vQuoteChar As Variant _
    ) As String

Code Example

    CharactersToSQLInExpression("ABC", "'") = "In ('A', 'B', 'C')"
    CharactersToSQLInExpression("A", "'") = "= 'A'"
    CharactersToSQLInExpression("A") = "= ""A"""
    CharactersToSQLInExpression("A'", "'") = "In ('A', '''')" ' #4
    CharactersToSQLInExpression("ABB", "'") = "In ('A', 'B')" ' #5
    CharactersToSQLInExpression(Null) = "Is Null"

Parameters

Parameter Description
vChars String containing characters which are to be converted into a SQL WHERE IN expression. Function returns the string "Is Null" is vChars is Null.
vQuoteChar String containing the character used to quote strings in the SQL expression where this function is used. vQuoteChar defaults to the double-quote character (") if it is missing or Null or cannot be fixed up to a String. If one of the characters within vChars is the quote character vQuoteChar, then that character will appear twice as in example #4.

Notes & Version History

Note Duplicate characters within string vChars are ignored as in example #5. Function returns a string containing an equals expression if the string vChars is either empty or only one character long.