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

Question

Hey all I have the following code below that I've been working on:

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:

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.

UPDATE

Thanks to a kind forum member HERE I was able to update my JSFIDDLE with my attempt at trying to recreate what he did:

[ { word: 'SELECT', index: 0, text: 'pillers, Balloons, Tacks' },
  { word: 'FROM', index: 32, text: 'the_database_file' },
  { word: 'WHERE', index: 55, text: 'Balloons != \'small\'' },
  { word: 'AND', index: 81, text: 'Balloons != \'large\'' },
  { word: 'AND', index: 105, text: 'Blah = \'bobby\'' },
  { word: ';', index: 123 } ]

However, my attempt did not come out the same. Any help?

score 0 · Answer 1 · answered Feb 24 '16 at 15:37

You implementation is really tricky, and I can imagine a lot of cases, when it will fail to parse.

I'd suggest not to re-invent the wheel, and to use existing libraries to parse the SQL with JS:

and others. There are a lot ready solution of different complexities.

score 0 · Accepted Answer · answered Feb 24 '16 at 19:51

Found the solution:

Update JSFIDDLE

function parseSql(sql) {
  var found = [];

  ["SELECT", "WHERE", "FROM", "AND", "OR", ";"].forEach(function(word) {
    var idx = sql.indexOf(word);

    while(idx!==-1) {      
      found.push({word:word, index:idx});
      idx = sql.indexOf(word, idx + 1);
      keptIdx = idx;
    }
  });

  found.sort(function(x,y) { return x.index - y.index });
  found.forEach(function(x, i, xs) {
    if (i < xs.length - 1) {
      x.text = sql.substring(x.index, xs[i + 1].index).replace(xs[i].word, "").trim();
    }
  });

  return found;
}

what if the KEY words will be in lower case - no detection? Or for example there will be the query alike "SELECT t1.countryFrom as `from` FROM t1 ....". Your parcer will fail on the first "from" mention. It's not good approach, as I told below in my answer. — Farside, Feb 25 '16 at 13:42

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

2 Answers2