Embedded Single Quotes

[MikeS]

I've been noticing a lot of posters that are having problems with Quotes/DoubleQuotes. In SQL Server 6.5 you should always use single quotes. Use this function to add another single quote so you don't have an odd number of quotes. (i.e. Change 'McDonald's' to 'McDonald''s')

Function CheckQuote(ByVal Str As String) As String
'*********************************************************
' This function checks for single quotes in strings and then
' inserts another quote beside it for exporting purposes.
' (Checks for only 2 singles.)
'*********************************************************

'Set the default value for the function
Str = Trim(Str)
CheckQuote = Str

'Check for NULL
If IsNull(Str) Then Exit Function

'Check quote
qpos = InStr(1, Str, "'")
If qpos = 0 Then Exit Function

'Replace single quote with two single quotes
Str = Left(Str, qpos) & "'" & Mid(Str, qpos + 1)

'Store what you have done in two vars.
t_start = Left(Str, qpos)
t_remainder = Mid(Str, qpos + 1)

'Check for another single quote in t_remainder
qpos2 = InStr(2, t_remainder, "'")
If qpos2 <> 0 Then
str2 = Left(t_remainder, qpos2) & "'" & Mid(t_remainder, qpos2 + 1)
Str = t_start & str2
End If

'Set the final value for the function
CheckQuote = Str

End Function

HTH
Seens

[JHausmann]

It depends. If I think the data will contain a single quote, I will use a double quote as a delimiter. Some examples might be names and addresses. If I know the field won't contain a single quote, a field containing states, zipcodes or phone numbers (for example) then I use single quotes.