Results 1 to 2 of 2

Thread: Splitting query on key words (SELECT/FROM/WHERE/AND/OR)

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2003
    Posts
    227

    Question Splitting query on key words (SELECT/FROM/WHERE/AND/OR)

    Hey all I have the following code below that I've been working on:
    Code:
    var sql = "SELECT pillers, Balloons, Tacks FROM the_database_file WHERE Balloons != 'small' AND Balloons != 'large' AND Blah = 'bobby';";
    
    $(document).ready(function() {
        var findFROM        = sql.indexOf(" FROM");
        var findWHERE       = sql.indexOf(" WHERE");
        var findAND         = sql.indexOf(" AND");
        var findOR          = sql.indexOf(" OR");
        var findSemicolon   = sql.indexOf(";"); 
        var findCountAND    = sql.match(/\AND\b/g);
        var findCountOR     = sql.match(/\OR\b/g);
    
        var txtSELECT       = sql.substring(0, findFROM);
        var txtFROM         = sql.substring(findFROM, findWHERE);
        var txtWHERE        = "";
        var txtAND          = "";
        var txtOR           = "";
    
        if (findAND != -1) {
            var _tmpPos     = 0;
            var _tmpAND     = "";
    
            findCountAND    = (findCountAND? findCountAND.length : 0);
            findCountOR     = (findCountOR? findCountOR.length : 0);
    
            for (var i = 1; i < findCountAND; i++) {
                console.log(i);
                _tmpPos = nth_occurrence(sql, ' AND', i);
                _tmpPos = findAND;
                findAND = sql.indexOf(" AND");
                _tmpAND = sql.substring(_tmpPos, findAND);
            }
    
            txtWHERE    = sql.substring(findWHERE, findAND);
        }
    
        $('#SELECT').text(txtSELECT);
        $('#FROM').text(txtFROM);
        $('#WHERE').text(txtWHERE);
        $('#test').text(findAND);
    });
    
    function nth_occurrence(string, char, nth) {
        var first_index = string.indexOf(char);
        var length_up_to_first_index = first_index + 1;
    
        if (nth == 1) {
            return first_index;
        } else {
            var string_after_first_occurrence = string.slice(length_up_to_first_index);
            var next_occurrence = nth_occurrence(string_after_first_occurrence, char, nth - 1);
    
            if (next_occurrence === -1) {
                return -1;
            } else {
                return length_up_to_first_index + next_occurrence;  
            }
        }
    }
    I am trying to format it like this:
    Code:
    SELECT pillers, Balloons, Tacks
    FROM the_database_file
    WHERE Balloons != 'small'
    AND Balloons != 'large'
    AND Blah = 'bobby';
    However, I am getting stuck in the area of trying to find more than one AND or OR in the query string. The example above just has 2 AND's to start out with but I cant seem to get the correct code.

    The nth_occurrence function was found HERE.

    Would be great if someone could help me out. Possibly a REGEX would be all that would be needed? Who knows?

    A JSFIDDLE has been set up as well.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Splitting query on key words (SELECT/FROM/WHERE/AND/OR)

    Take a look at the following function:
    Code:
    //parameter input: The SQL string command to be formatted
      function formatSQL(input) {
        var keywords = input.replace(/(^|\s)(select|from|where|and|or)(\s|$)/igm, function (match) {
            return "\n" + match ;
        }      
     		);
        var blankLines = keywords.replace(/^\s*\n^/gm, "");
        var leadingWS = blankLines.replace(/\n\s/gm, "\n");
        
        return leadingWS;
      };
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width