-
Feb 24th, 2016, 10:20 AM
#1
Thread Starter
Addicted Member
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.
-
Feb 25th, 2016, 01:41 PM
#2
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;
};
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|