An SQL Question...!!!!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: An SQL Question...!!!!

  1. #1
    Jim Guest

    An SQL Question...!!!!


    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

  2. #2
    Andrew Guest

    Re: An SQL Question...!!!!


    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



  3. #3
    andrew oneill Guest

    Re: An SQL Question...!!!!


    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



  4. #4
    Jim Guest

    Re: An SQL Question...!!!!


    --> 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

    >



  5. #5
    Arthur Wood Guest

    Re: An SQL Question...!!!!


    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

    >>

    >



  6. #6
    andrew ONeill Guest

    Re: An SQL Question...!!!!


    >>--> 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

  7. #7
    andrew ONeill Guest

    Re: An SQL Question...!!!!



    >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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center