Analyze a string and determine an SQL operator

Question

I need your help.

I'd like to write a function that would detect and determine the operator (LIKE/=) to use in an SQL string

Example:

var input_string = "SELECT * from table WHERE [FIRSTNAME]"

var input_text = document.GetElementbyId('firstname').value

if 1 of the 4 possible combinations are met from the given input_text then the operator value will be "LIKE", else it is an equal sign (=)

1.) At the start of the string:  %firstname

2.) somewhere in the string itself: first%name

3.) At the end of the string: firstname%

4.) at both ends of the string: %firstname%

Example:

var output_string = input_string + analyze(input_text) '+input_text+'

var output_string examples:

SELECT * FROM TABLE WHERE [FIRSTNAME] LIKE '%firstname%'
SELECT * FROM TABLE WHERE [FIRSTNAME] LIKE '%first%name'
SELECT * FROM TABLE WHERE [FIRSTNAME] LIKE 'firstname%'
SELECT * FROM TABLE WHERE [FIRSTNAME] LIKE '%firstname%'

else

SELECT * FROM TABLE WHERE [FIRSTNAME] = 'firstname'

You can just always use `LIKE`. Doing `SELECT * FROM table WHERE firstname LIKE 'John'` should work fine. — gen_Eric, Sep 30 '13 at 20:04
A little off topic, why are you constructing SQL statements in JS? (curious). — tymeJV, Sep 30 '13 at 20:04
I am making a simle HTA application to grab some info from an access DB and display it back to the user. — John Smith, Sep 30 '13 at 20:05

score 0 · Answer 1 · answered Sep 30 '13 at 20:05

0

Since using like depends on having the % you should simply check for the % character within your string using indexOf method.

Example:

function useLike(input_text)
{
  return input_text.indexOf('%')>-1;
}

answered Sep 30 '13 at 20:05

Menelaos

23,508
18
90
155

score 0 · Accepted Answer · answered Sep 30 '13 at 20:08

0

You seem to want

function analyze(compare) {
    if (compare.indexOf("%") > -1)
        return "LIKE";
    else
        return "=";
    // or shorter with a regex and conditional operator:
    return /%/.test(compare) ? "LIKE" : "=";
}
var output_string = input_string+" "+analyze(input_text)+"'"+input_text+"'";

However, as @RocketHazmat mentioned in the comments, you could just use LIKE always and it would work like = when there are no percent signs in the string.

answered Sep 30 '13 at 20:08

Bergi

630,263
148
957
1,375

Using `=` is faster if you don't have wildcards. http://stackoverflow.com/questions/559506/mysql-using-like-vs-for-exact-string-match – Menelaos Sep 30 '13 at 20:13
Is it? To cite paxdiablo, "*In a decent DBMS (e.g., DB2 :-), the DB engine would recognize that there were no wildcard characters in the string and implicitly turn it into an `=` version*". And that was 2009. – Bergi Sep 30 '13 at 20:21
... you know that phrase about assumptions :)... but anyway, speed difference even if DB does not check will be negligible. – Menelaos Sep 30 '13 at 20:22

score 0 · Answer 3 · answered Sep 30 '13 at 20:09

0

You should just be able to use LIKE regardless.

SELECT * FROM table WHERE firstname LIKE 'John'

Is a valid query.

answered Sep 30 '13 at 20:09

gen_Eric

223,194
41
299
337

Analyze a string and determine an SQL operator

3 Answers3