An SQL Question...!!!!

[Jim]

I'm querying on a name field and the last name has an ' in it like: (O'Brian).
In the sql statement it bombs. How do I tell the sql statement to ignore
the ' and except the O'Brian as a string? Let me know if this isn't clear.!!!!
Thanks for your help..!!!!

Jim

[Andrew]

Hi Jim

check out VB General. There has been a lot on this subject under RE:Apostrophes
in SQL strings and RE using ' in sql statements

"Jim" <jcb1269@excite.com> wrote:
>
>I'm querying on a name field and the last name has an ' in it like: (O'Brian).
> In the sql statement it bombs. How do I tell the sql statement to ignore
>the ' and except the O'Brian as a string? Let me know if this isn't clear.!!!!
> Thanks for your help..!!!!
>
>Jim

[andrew oneill]

You need to substitute two apostrophes.
It's common to have a function which does this to a given string.
With VB, you'd use instr to look for em and work your way through the entire
string in a loop.

Usually, I leave the apostrophe out my name when I use apps where it doesn't
really matter.


"Jim" <jcb1269@excite.com> wrote:
>
>I'm querying on a name field and the last name has an ' in it like: (O'Brian).
> In the sql statement it bombs. How do I tell the sql statement to ignore
>the ' and except the O'Brian as a string? Let me know if this isn't clear.!!!!
> Thanks for your help..!!!!
>
>Jim

[Jim]

--> Do you know where I can find examples?

"andrew oneill" <andrew.oneill@intervoice-brite.co.uk> wrote:
>
>You need to substitute two apostrophes.
>It's common to have a function which does this to a given string.
>With VB, you'd use instr to look for em and work your way through the entire
>string in a loop.
>
>Usually, I leave the apostrophe out my name when I use apps where it doesn't
>really matter.
>
>
>"Jim" <jcb1269@excite.com> wrote:
>>
>>I'm querying on a name field and the last name has an ' in it like: (O'Brian).
>> In the sql statement it bombs. How do I tell the sql statement to ignore
>>the ' and except the O'Brian as a string? Let me know if this isn't clear.!!!!
>> Thanks for your help..!!!!
>>
>>Jim
>

[Arthur Wood]

Jim,

You can use the Replace function in VB6 to replace ALL occurences if (')
with ('') in the string which is serving as the parameter in your Where Clause:

StrSQL = "Select * from Table Where Name = '" & Replace(strName,"'","''")
& "'"

That should solve the problem.

This,by the way, is a VERY VERY frequently asked question (appears somewhere
on these various discussion borads about once or twice a month, on average).

Arthur Wood


"Jim" <jcb1269@excite.com> wrote:
>
>--> Do you know where I can find examples?
>
>"andrew oneill" <andrew.oneill@intervoice-brite.co.uk> wrote:
>>
>>You need to substitute two apostrophes.
>>It's common to have a function which does this to a given string.
>>With VB, you'd use instr to look for em and work your way through the entire
>>string in a loop.
>>
>>Usually, I leave the apostrophe out my name when I use apps where it doesn't
>>really matter.
>>
>>
>>"Jim" <jcb1269@excite.com> wrote:
>>>
>>>I'm querying on a name field and the last name has an ' in it like: (O'Brian).
>>> In the sql statement it bombs. How do I tell the sql statement to ignore
>>>the ' and except the O'Brian as a string? Let me know if this isn't clear.!!!!
>>> Thanks for your help..!!!!
>>>
>>>Jim
>>
>

[andrew ONeill]

>>--> Do you know where I can find examples?

Well... it might have been clearer if you'd specified the language you're
using but...

If you're using vbscript & ASP...
Quite frankly, I don't think I've ever read the following code with any care,
it was a "standard" procedure I just slung in my bag of tricks a while back.
I have no idea why the author didn't use replace... maybe it wasn't in whichever
version of vbscript <shrug>

Function Quotes(strWord As String) As String
' This function inserts an extra quote where a quote appears in the string
' This was introduced to accomodate names with quotes in them since the like
statement
' didn't work properly with those names.
Dim intCounter, strNewWord, strTemp

If (InStr(strWord, "'") > 0) Or (InStr(strWord, "&quot") > 0) Then
strNewWord = ""
For intCounter = 1 To Len(strWord)
strTemp = Mid(strWord, intCounter, 1)
strNewWord = strNewWord & strTemp
If strTemp = "'" Then
strNewWord = strNewWord & "'"
ElseIf strTemp = "&quot" Then
strNewWord = strNewWord & "&quot"
End If
Next
Quotes = strNewWord
Else
Quotes = strWord
End If
End Function

[andrew ONeill]

>Well... it might have been clearer if you'd specified the language you're
>using but...

Ooops
Obviously too early in the morning for me...