Quote characters in sql (1 Viewer)

David44Coder

Member
Local time
Tomorrow, 05:10
Joined
May 20, 2022
Messages
112
leftCurly = Encodings.UTF8.chr(&U201C)rightCurly = Encodings.UTF8.chr(&U201D)

Can I convert/use these in sql in Access anyhow?

sql = "INSERT INTO " & y & " (Serial, Lyrics) values (" & Chr$(34) & Ser & Chr$(34) & "," & Chr$(34) & TheLyric & Chr$(34) & ")"
CurrentDb.Execute sql, dbFailOnError

An error occurs if TheLyric contains embedded chr$(34)s.
 
What happens if you use Chr$(39) instead?
 
Data with embedded quote or apostrophe can complicate SQL concatenation. You say data has quote mark, does any have apostrophe - as in O'Hare?

Alternatives that don't care if data has special characters:

1. parameterized SQL

2. update/insert a recordset
 
Last edited:
An error occurs if TheLyric contains embedded chr$(34)s.

Either use:
Code:
sql = "INSERT INTO " & y & " (Serial, Lyrics) values ('" & Ser & "','" & TheLyric & "')"
or
Code:
sql = "INSERT INTO " & y & " (Serial, Lyrics) values (" & Chr$(34) & Replace(Ser, Chr(34), Chr(34) & chr(34)) & Chr$(34) & "," & Chr$(34) & Replace(TheLyric, Chr(34), Chr(34) & chr(34)) & Chr$(34) & ")"
 
Many thanks for the replies/.ideas. There are apostrophes as well. cheekybuddha's second sql worked. :)
I'd forgotten about parameters, I'll dig out some examples and try that too.
 
There are apostrophes as well.
Then use:
Code:
sql = "INSERT INTO " & y & " (Serial, Lyrics) values ('" & Replace(Ser, "'", "''") & "','" & Replace(TheLyric, "'", "''") & "')"
It's more readable than all the Chr$(34)'s
 
Or add the function found in this post to a standard module, and then you can just use:
Code:
sql = "INSERT INTO " & y & " (Serial, Lyrics) VALUES (" & SQLStr(Ser) & "," & SQLStr(TheLyric) & ")"
Much simpler and cleaner.
 
or you can copy this in a module:
Code:
'http://bytes.com/topic/access/answers/211263-double-quotes-data
'by: Bruce Rusk
'Usage is as follows:
'
'strSQL = "SELECT * FROM tblPeople WHERE LastName Like " & SQLQuote
'(strString) & ";"
'
'you can also have it add wildcards to modify the search by adding additional
'boolean arguments:
'
'for a wildcard before the string (search for "*endofname"):
'
'strSQL = "SELECT * FROM tblPeople WHERE LastName Like " & SQLQuote
'(strString, True) & ";"
'
'for a wildcard after the string (search for "startofname*"):
'
'strSQL = "SELECT * FROM tblPeople WHERE LastName Like " & SQLQuote
'(strString, , True) & ";"

'--------------Begin Code---------------

Const SingleQuote = "'"
Const DoubleQuote = """"
Const StarSingleQuote As String = "*'"
Const SingleQuoteStar As String = "'*"
Const DoubleQuoteStar As String = """" & "*"
Const StarDoubleQuote As String = "*" & """"
Const CHRDouble As String = "' & CHR(34) & '"
Const CHRSingle As String = "' & CHR(39) & '"

Public Function SQLQuote(ByVal strIn As String, _
                         Optional ByVal PrefixWildCard As Boolean = False, _
                         Optional ByVal SuffixWildcard As Boolean = False _
                       ) As String

    Dim blnSingleQuote As Boolean
    Dim blnDoubleQuote As Boolean
    Dim blnCHRStart As Boolean
    Dim blnCHREnd As Boolean
    Dim strCompare As String

    blnDoubleQuote = (InStr(strIn, DoubleQuote) <> 0)

    If blnDoubleQuote Then
        ' There are double quotes; check for single
        ' quotes as well

        blnSingleQuote = (InStr(strIn, SingleQuote) <> 0)

        If blnSingleQuote Then
            ' Both single AND double quotes -- complicated,
            ' replace with CHR functions

            ' Replace single quotes with CHR version
            strCompare = Replace$(strIn, SingleQuote, CHRSingle)

            ' Clean up front and back
            If Left$(strCompare, 15) = CHRSingle Then _
               strCompare = Mid$(strCompare, 5)

            If Right$(strCompare, 15) = CHRSingle Then _
               strCompare = Left$(strCompare, Len(strCompare) - 4)

            ' Replace double quotes with CHR version
            strCompare = Replace$(strCompare, DoubleQuote, CHRDouble)

            ' Clean up front and back
            If Left$(strCompare, 15) = CHRDouble Then _
               strCompare = Mid$(strCompare, 5)

            If Right$(strCompare, 15) = CHRDouble Then _
               strCompare = Left$(strCompare, Len(strCompare) - 4)

            ' String parsed, now check for wildcards

            ' First, check whether the string starts or ends with
            ' a CHR(n) value

            blnCHREnd = (StrComp(Right$(strCompare, 8), " CHR(34)")) = 0 Or _
                        (StrComp(Right$(strCompare, 8), " CHR(39)")) = 0

            blnCHRStart = Left$(strCompare, 5) = "CHR(3"

            ' Add wildcard and start/end single quotes, as
            ' needed
            '
            If PrefixWildCard Then
                If blnCHRStart Then
                    strCompare = "'*' & " & strCompare
                Else
                    strCompare = SingleQuoteStar & strCompare
                End If

            ElseIf Not blnCHRStart Then
                strCompare = SingleQuote & strCompare

            End If

            If SuffixWildcard Then

                If blnCHREnd Then
                    strCompare = strCompare & " & '*'"
                Else
                    strCompare = strCompare & StarSingleQuote
                End If

            ElseIf Not blnCHREnd Then
                strCompare = strCompare & SingleQuote

            End If

        Else
            ' Only double quotes, so using single
            ' quotes around it is safe

            If PrefixWildCard Then
                strCompare = SingleQuoteStar & strIn
            Else
                strCompare = SingleQuote & strIn
            End If

            If SuffixWildcard Then
                strCompare = strCompare & StarSingleQuote
            Else
                strCompare = strCompare & SingleQuote
            End If
        End If
    Else
        ' No double quotes in the string
        ' so using double quotes around it
        ' is safe (even if there are single
        ' quotes in it).
        '
        If PrefixWildCard Then
            strCompare = DoubleQuoteStar & strIn
        Else
            strCompare = DoubleQuote & strIn
        End If

        If SuffixWildcard Then
            strCompare = strCompare & StarDoubleQuote
        Else
            strCompare = strCompare & DoubleQuote
        End If

    End If

    SQLQuote = strCompare

End Function

'----------------End Code-------------------

to use in your Query string:
Code:
sql = "INSERT INTO " & y & " (Serial, Lyrics) values (" & SQLQuote(ser) & ", " & SQLQuote(TheLyric) & ")"
CurrentDb.Execute sql, dbFailOnError
 

Users who are viewing this thread

Back
Top Bottom