none
find whole words in string RRS feed

  • Question

  • Thanks for taking the time to read my question.

    I've looked on line for some sort of solution, but can't seem to search the right thing...

    I have a string that contains a bunch of words, any words, any length. There are also other characters like ( ( . * and ;

    I want to put quotes around just whole words, except for key words.

    What I'm trying to do is make something that will export queries I build in Access to MySQL. So I want to do this

    Make this:

    SELECT tblTest.field1, tblTest.field2
    FROM tblTest
    WHERE (((tblTest.field1)=1 AND ((tblTest.field2)="test"));

    this:

    SELECT `tblTest`.`field1`, `tblTest`.`field2`
    FROM `tblTest`
    WHERE (((`tblTest`.`field1`)=1 AND ((`tblTest`.`field2`)="test"));

    If I can find full words like tblTest, then I can wrap them in quotes, and make a list of key words not to put quotes around. I'm guessing that by searching for words, it will not select characters like I've listed above.

    At the moment, I'm using Replace(), but the number of Replace()'s that I'm using is getting out of hand and it's not very flexible.

    ... just had a thought here... or maybe I use Replace() but there's a way to do wild card searches for full words?

    Thanks,
    Brad

    Tuesday, June 19, 2012 9:52 PM

Answers

  • You have the same condition twice in the first If statement.

    I am concerned that you will run into a fullword that is just part of a reserved word and get an invalid match.  I think I would record that part so it looks more like:

    ReservedWords = "/SELECT/FROM/WHERE/INTO/INSERT/" _
                & "UPDATE/DELETE/INNER/JOIN/ON/AND/OR/SET/"
    and

    If InStr(1, ReservedWords, "/" & FullWord & "/", _
                                   vbBinaryCompare) = 0 Then

    Thursday, June 21, 2012 7:19 PM
  • It's not jumping out at me why you are getting a zero length string in `.  Maybe looking carefully at the debug output will point out what situation the code was dealing with when it did that.

    I forgot to explain about case sensitivity earlier so here it is now.  Access is NOT case sensitive unless you do something, such as using vbBinaryCompare, to make it do so.  Even then, all keywords, especially those in SQL are NOT case sensitive.

    In reviewing your code, the two conditions in the first If statement are equivalent and your use of vbBinaryCompare in the InStr and Replace functions is not needed.  Usin vbBinaryCompare in the InStr function may not find a match when it should find one.

    Going off on a possible side tangent, I don't like the way you are handling quited strings.  Access SQL recorgnized both " and ' as string delimiters and within the string, two of those delimiters together indicate that the doubled up internal delimiter represents the real character and is not a delimiter.  For example:  the string
       He said, "Eat at Joe's."
    can be written in SQL in either of two ways:
       "He said, ""Eat at Joe's."""
    or
       'He said, "Eat at Joe''s."'
    and I don't see where your code attempts to deal with either.

    I think I would consider dealing with quotes differently by when either one is detected, use an inner loop to chew through characters looking for a matching quote and, if it's not doubled up, resume looking for another full word.


    Friday, June 22, 2012 6:57 PM

All replies

  • Hi Brad,

    I don't know how you would do this without some rather complicated code and excuse my ignorance as I have not used MySQL from Access but is it necessary to enclose each table and field name in apostrophes with MySQL?

    Regards,

    Ray


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for changing her words.

    Wednesday, June 20, 2012 1:17 AM
  • Thanks for the reply Ray,

    When I look at how phpMyAdmin makes the queries, it puts all field names in `` those kind of quotes. If I don't put the field names / table names in those kind of quotes the queries won't run in phpMyAdmin. So therefore I'm assuming they need to be there.

    I was hoping there was some kind of standard function out there that found whole words... I guess I'll just keep doing what I'm doing unless someone posts something different.

    thanks,

    Brad

    Wednesday, June 20, 2012 1:01 PM
  • If you use the query designer to create/modify a query, it blindly adds [ ] around every table and field name.  If you follow the same practice when you create a query in SQL view or VBA code, then you only need to replace those two characters with '
    Wednesday, June 20, 2012 1:51 PM
  • Thanks for the reply Marshall,

    I'm using the following to get the sql string in my code. Is there a better way that makes the [ and ] show up?

    Set qdf = dbs.QueryDefs(varItem)
                Debug.Print qdf.Name
                Debug.Print qdf.SQL
               
                strSQL = qdf.SQL

    strSQL then equals

    SELECT tblJobs.JobID, tblJobs.JobName, tblJobs.JobActive, tblJobs.JobUpdatedBy, tblJobs.JobUpdatedDate, tblJobs.JobVisibleLevel, tblFile.FileID, tblFile.FilePath, tblFile.FileActive, tblFile.FileVisibleLevel, tblFile.tblJobID
    FROM tblJobs INNER JOIN tblFile ON tblJobs.JobID = tblFile.tblJobID
    WHERE (((tblJobs.JobID)=3) AND ((tblJobs.JobName)="Brad")) OR (((tblJobs.JobActive)=True));

    This is my test query...

    Thanks,

    Brad

    Wednesday, June 20, 2012 2:02 PM
  • You should be able to manually open the query in design view, make a meaningless change (e.g. add a space somewhere) and save the query.  View the query's datasheet to make sure it still does what it used to do.  Then run your test.

    If you don't use the query designer, just remember to type the [ ] as you create the query in VBA or in SQL View.

    If you are working with a lot of predefined Access SQL dialect queries that do not have the [ ] in their SQL view, then you are back to your oringinal question.  OTOH, in general, I would not expect some other database engine to run any old Access query without additional changes so you'll have a bunch or other issues bedides the ' arounf field and table names.

    Wednesday, June 20, 2012 4:52 PM
  • Hi,

    I've written a function. Are there any improvements that anyone can spot?

    Function FindWholeWord(Stmt As String) As String
    Dim x As Integer, y As Integer
    Dim StmtLen As Integer
    Dim newStmt As String, FullWord As String
    Dim ReservedWords As String
    Dim isDoubleQuoteOpen As Boolean, isDoubleQuoteClose As Boolean

    StmtLen = Len(Stmt)

    y = 1 'Start of substring
    ReservedWords = "SELECT FROM WHERE INTO INSERT UPDATE DELETE INNER JOIN ON AND OR SET"


        For x = 1 To StmtLen
            If Mid(Stmt, x, 1) Like "[A-Z]" = False And Mid(Stmt, x, 1) Like "[A-Z]" = False Then 'Find out if it's a letter of the alphabet
                If Mid(Stmt, x, 1) = Chr(34) Then
                    If isDoubleQuoteOpen = False And isDoubleQuoteClose = False Then
                        isDoubleQuoteOpen = True
                    ElseIf isDoubleQuoteOpen = True And isDoubleQuoteClose = False Then
                        isDoubleQuoteClose = True
                    Else
                        isDoubleQuoteOpen = True
                        isDoubleQuoteClose = False
                    End If
                End If
                If Mid(Stmt, x, 1) = Chr(13) Or Mid(Stmt, x, 1) = Chr(10) Then
                    FullWord = Mid(Stmt, y, x - y)
                    If FullWord = "True" Or FullWord = "False" Then
                        If FullWord = "True" Then
                            FullWord = 1
                        Else
                            FullWord = 2
                        End If
                    Else
                        If InStr(1, ReservedWords, FullWord, vbBinaryCompare) < 1 Then
                            If isDoubleQuoteOpen = False And isDoubleQuoteClose = False Then
                                FullWord = "`" & FullWord & "`"
                            End If
                        End If
                    End If
                    newStmt = newStmt & FullWord & vbCrLf
                    x = x + 1
                    y = x + 1
                Else
                    FullWord = Mid(Stmt, y, x - y)
                    If FullWord = "True" Or FullWord = "False" Then
                        If FullWord = "True" Then
                            FullWord = 1
                        Else
                            FullWord = 2
                        End If
                    Else
                        If InStr(1, ReservedWords, FullWord, vbBinaryCompare) < 1 Then
                            If isDoubleQuoteOpen = False And isDoubleQuoteClose = False Then
                                FullWord = "`" & FullWord & "`"
                            End If
                        End If
                    End If
                    newStmt = newStmt & FullWord & Mid(Stmt, x, 1)
                    y = x + 1
                End If
            End If

        Next x

        newStmt = Replace(newStmt, "( ", "(", 1, , vbBinaryCompare)
        newStmt = Replace(newStmt, " )", ")", 1, , vbBinaryCompare)
        FindWholeWord = newStmt
       
    End Function

    Thursday, June 21, 2012 2:50 PM
  • You have the same condition twice in the first If statement.

    I am concerned that you will run into a fullword that is just part of a reserved word and get an invalid match.  I think I would record that part so it looks more like:

    ReservedWords = "/SELECT/FROM/WHERE/INTO/INSERT/" _
                & "UPDATE/DELETE/INNER/JOIN/ON/AND/OR/SET/"
    and

    If InStr(1, ReservedWords, "/" & FullWord & "/", _
                                   vbBinaryCompare) = 0 Then

    Thursday, June 21, 2012 7:19 PM
  • ah, yes... I forgot to change that back.

    Is [a-z] the same or different from [A-Z]. I thought it was case sensitive.

    I will also update the ReservedWords as you have noted.

    Thanks Marshall!

    Brad

    Thursday, June 21, 2012 7:54 PM
  • Hi Marshall,

    I did

    ReservedWords = "/SELECT/FROM/WHERE/INTO/INSERT/" _
                & "UPDATE/DELETE/INNER/JOIN/ON/AND/OR/SET/"
    and

    If InStr(1, ReservedWords, "/" & FullWord & "/", _
                                   vbBinaryCompare) = 0 Then

    now I get `` some times in my sql... I can't figure out why.

    I changed it back and now it works.

    Any thoughts?

    Thursday, June 21, 2012 9:56 PM
  • Look at the words that are getting the extra `` there may be a clue in there.  The problem with not using a change like I suggested is that you would not have ` around words (field/table names) like IN, DATE, ATE, DEL, etc.

    If you are getting ` around a zero length string, then there is a logic problem in your code.

    Friday, June 22, 2012 1:07 PM
  • Updated Function:

    Function FindWholeWord(Stmt As String) As String
    Dim x As Integer, y As Integer
    Dim StmtLen As Integer
    Dim newStmt As String, FullWord As String
    Dim ReservedWords As String
    Dim isDoubleQuoteOpen As Boolean, isDoubleQuoteClose As Boolean

    StmtLen = Len(Stmt)

    y = 1 'Start of substring
    ReservedWords = "/SELECT/FROM/WHERE/INTO/INSERT/UPDATE/DELETE/INNER/JOIN/ON/AND/OR/SET/"


        For x = 1 To StmtLen
            'Debug.Print Mid(Stmt, x, 1)
            If Mid(Stmt, x, 1) Like "[a-z]" = False And Mid(Stmt, x, 1) Like "[A-Z]" = False Then 'Find out if it's a letter of the alphabet
                If Mid(Stmt, x, 1) = Chr(34) Then
                    If isDoubleQuoteOpen = False And isDoubleQuoteClose = False Then
                        isDoubleQuoteOpen = True
                    ElseIf isDoubleQuoteOpen = True And isDoubleQuoteClose = False Then
                        isDoubleQuoteClose = True
                    Else
                        isDoubleQuoteOpen = True
                        isDoubleQuoteClose = False
                    End If
                End If
                If Mid(Stmt, x, 1) = Chr(13) Or Mid(Stmt, x, 1) = Chr(10) Then
                    FullWord = Mid(Stmt, y, x - y)
                    If FullWord <> "" Then
                        If FullWord = "True" Or FullWord = "False" Then
                            If FullWord = "True" Then
                                FullWord = 1
                            Else
                                FullWord = 2
                            End If
                        Else
                            If InStr(1, ReservedWords, "/" & FullWord & "/", vbBinaryCompare) < 1 Then
                                If isDoubleQuoteOpen = False And isDoubleQuoteClose = False Then
                                    FullWord = "`" & FullWord & "`"
                                End If
                            End If
                        End If
                        newStmt = newStmt & FullWord & vbCrLf
                    Else
                        newStmt = newStmt & vbCrLf
                    End If
                    x = x + 1
                    y = x + 1
                Else
                    FullWord = Mid(Stmt, y, x - y)
                    If FullWord <> "" Then
                        If FullWord = "True" Or FullWord = "False" Then
                            If FullWord = "True" Then
                                FullWord = 1
                            Else
                                FullWord = 2
                            End If
                        Else
                            If InStr(1, ReservedWords, "/" & FullWord & "/", vbBinaryCompare) < 1 Then
                                If isDoubleQuoteOpen = False And isDoubleQuoteClose = False Then
                                    FullWord = "`" & FullWord & "`"
                                End If
                            End If
                        End If
                        newStmt = newStmt & FullWord & Mid(Stmt, x, 1)
                    Else
                        newStmt = newStmt & Mid(Stmt, x, 1)
                    End If
                    y = x + 1
                End If
                Debug.Print newStmt & "|"
            End If

        Next x

        newStmt = Replace(newStmt, "( ", "(", 1, , vbBinaryCompare)
        newStmt = Replace(newStmt, " )", ")", 1, , vbBinaryCompare)
        FindWholeWord = newStmt
       
    End Function

    Friday, June 22, 2012 4:18 PM
  • It's not jumping out at me why you are getting a zero length string in `.  Maybe looking carefully at the debug output will point out what situation the code was dealing with when it did that.

    I forgot to explain about case sensitivity earlier so here it is now.  Access is NOT case sensitive unless you do something, such as using vbBinaryCompare, to make it do so.  Even then, all keywords, especially those in SQL are NOT case sensitive.

    In reviewing your code, the two conditions in the first If statement are equivalent and your use of vbBinaryCompare in the InStr and Replace functions is not needed.  Usin vbBinaryCompare in the InStr function may not find a match when it should find one.

    Going off on a possible side tangent, I don't like the way you are handling quited strings.  Access SQL recorgnized both " and ' as string delimiters and within the string, two of those delimiters together indicate that the doubled up internal delimiter represents the real character and is not a delimiter.  For example:  the string
       He said, "Eat at Joe's."
    can be written in SQL in either of two ways:
       "He said, ""Eat at Joe's."""
    or
       'He said, "Eat at Joe''s."'
    and I don't see where your code attempts to deal with either.

    I think I would consider dealing with quotes differently by when either one is detected, use an inner loop to chew through characters looking for a matching quote and, if it's not doubled up, resume looking for another full word.


    Friday, June 22, 2012 6:57 PM
  • Hi Marshall,

    Thanks for the clairification on case sensitivity. I'll take the duplication out.

    I was thinking of an inner loop, but was not sure if that was the best way to handle it. I'll give it a shot. Thanks for pointing that out.

    Have a great weekend!

    Brad

    Friday, June 22, 2012 7:51 PM