c# sqlite command formatting

Question

In my database I have an entry ABCCBAA

And my Query looks like this:

    string sql = SELECT * FROM signatures WHERE length(signature) BETWEEN 6 AND 8 AND signature LIKE ABCCBAA 
        OR length(signature) BETWEEN 6 AND 8 AND signature LIKE _BCCBAA 
        OR length(signature) BETWEEN 6 AND 8 AND signature LIKE A_CCBAA 
        OR length(signature) BETWEEN 6 AND 8 AND signature LIKE AB_CBAA 
        OR length(signature) BETWEEN 6 AND 8 AND signature LIKE ABC_BAA 
        OR length(signature) BETWEEN 6 AND 8 AND signature LIKE ABCC_AA 
        OR length(signature) BETWEEN 6 AND 8 AND signature LIKE ABCCB_A 
        OR length(signature) BETWEEN 6 AND 8 AND signature LIKE ABCCBA_

I am searching through a lot of records with this 1 query so pardon the overkill.

I've been trying this query against the W3Schools examples and it works fine when put into context. I know I'm using SQLite and not SQL but not fully there yet with the syntax.

I expect any of those OR statements to fetch the result, but I have not even been able to execute it yet because of syntax errors. At the moment I am getting

"SQL logic error or missing database

no such column: ABCCBAA"

Why it is using it as a column name? Other examples lead me to believe the query is correct.

I've been playing about with this for a long time now, so it's possibly something simple I'm not grasping correctly.

It looks like you're trying to do a sort of fuzzy string matching. There are probably better approaches, if I were you I'd look it up, e.g. en.wikipedia.org/wiki/Approximate_string_matching — Tim S.
– Tim S., Commented Aug 22, 2013 at 23:06
_ matches a single character, so your query can never match something of a different length. — Tim S.
– Tim S., Commented Aug 22, 2013 at 23:16
@TimS. Thanks for pointing me to fuzzy matching, I'll see what can be done within sql queries. And with regards to lengths I have accommodated the problem with some more ugly code :) — Rodney
– Rodney, Commented Aug 22, 2013 at 23:19

Alon Gubkin · Accepted Answer · 2013-08-22 23:11:48Z

3

You need to quote the string, for example:

WHERE length(signature) BETWEEN 6 AND 8 AND signature LIKE 'ABCCBAA'

Also, you don't need so many OR length(signature) BETWEEN 6 AND 8. Just do something like:

string sql = @"SELECT * FROM signatures 
               WHERE length(signature) BETWEEN 6 AND 8 
               AND (signature LIKE 'ABCCBAA' OR signature LIKE '_BCCBAA' ...)";

edited Aug 22, 2013 at 23:11

answered Aug 22, 2013 at 23:04

Alon Gubkin

57.2k58 gold badges200 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rodney Over a year ago

I've tried doing that, but get a whole load of different syntax errors, the quotations play havoc, and escaping it with .Replace("'",@"''"), or as a parameter doesn't work, it never maps properly. I will try again anyway.

Alon Gubkin Over a year ago

Instead of escaping, you should use SQL Parameters

Rodney Over a year ago

Well that was silly... Solved all my problem by parameterising it properly. Thanks for the tips, I'll see what I can do to make it non-ugly.

Collectives™ on Stack Overflow

c# sqlite command formatting

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related