SQlite query not working when selecting integers in 1000s

Question

I'm using c#, winforms, SQlite as my database.

I'm running the following query:

string sql14 = "select YYMM, TotalTrans  from t2 where CAST(TotalTrans as int) < 500000 and CAST(TotalTrans as int) > 1000";

I then use this query to populate 2 lists, which I then export to a menustrip where the user can select it like this:

string sql14 = "select YYMM, TotalTrans  from t2 where CAST(TotalTrans as int) <  500000 and CAST(TotalTrans as int) > 1000";
SQLiteCommand cmd5 = new SQLiteCommand(sql14, sqlite_conn);


SQLiteDataReader rdr5 = cmd5.ExecuteReader();

while (rdr5.Read())
{

  int TotalTranst22;
  DateTime yyyymmt22;

  if (DateTime.TryParse(rdr5["YYMM"].ToString(), out yyyymmt22) && int.TryParse(rdr5["TotalTrans"].ToString(), out TotalTranst22))
  {
      // Populating the lists

      YYMMt22.Add(yyyymmt22);
      TotalTransIrregularitiest22.Add(TotalTranst22);
  }
}

Problem: When I select the option to show the contents of the 2 lists YMMt22 and TotalTransIrrefularitiest22, nothing happens. While trying to debug I found out that the lists are completely empty, i.e: the query isn't working.

Update:

The problem arises when I try to run the query for values > 1000, as they have a ',' in them.

Also, when trying a user suggestion (below)

SELECT CAST(TotalTrans as int) FROM t2`

I get an error saying the index was out of bounds of array in the condition of my if statement

Here is the actual data I'm trying to store in the lists via the query. As you can see, it does have thousands separators (the commas).

enter image description here

Just for the record: I know this process of querying my table works because I used a similar query to get values <1000 (notice they dont have a ',') and it worked perfectly. Here's the code for that:

// Total Trans tolerance <1000

string sql13 = "select YYMM, TotalTrans  from t2 where cast(TotalTrans as int) < 1000";

Update:

Tried a user solution... still no luck. The lists dont get filled with any data

    string sql14 = "select YYMM, TotalTrans  from t2 where CAST(REPLACE(TotalTrans, ',', '') as int) < 500000 and CAST(REPLACE(TotalTrans, ',', '') as int) > 1000";
        SQLiteCommand cmd5 = new SQLiteCommand(sql14, sqlite_conn);


        SQLiteDataReader rdr5 = cmd5.ExecuteReader();

        while (rdr5.Read())
        {

            int TotalTranst22;


            DateTime yyyymmt22;
            if (DateTime.TryParse(rdr5["YYMM"].ToString(), out yyyymmt22) && int.TryParse(rdr5["TotalTrans"].ToString(), out TotalTranst22))
            {
                YYMMt22.Add(yyyymmt22);
                TotalTransIrregularitiest22.Add(TotalTranst22);
            }
        }

Is TotalTrans a string? Might it be that it returns NULL if the string contains non-numeric characters like ','? What happens if you 'SELECT CAST(TotalTrans as int) FROM t2` ? Does it return the integers you expect, or a bunch of nulls as well? — oerkelens, Nov 27 '13 at 16:03
The cast "clearly" works? Then why do yuo have problems? For values < 1000 you do not have ',' in there, so it will work, yes. What do you get when you select what i asked? — oerkelens, Nov 27 '13 at 16:22
Ok: When I run your query I get the error: Index was out of bounds of array in the if statement in my original post.. interesting — Newuser, Nov 27 '13 at 16:45
Another key find: You were right about the ',' Anything >1000 (values with comma separator doesn't return any values from my query — Newuser, Nov 27 '13 at 16:47

dazedandconfused · Accepted Answer · 2013-12-05T13:32:37.857

3

Try something like the following, I don't have sqlite available right now so it may need a slight tweak...

EDIT: I noticed later that although you are converting to an int in the WHERE clause you are SELECTing the text version of TotalTrans which will have commas. This is causing your int.TryParse to fail. Try the following, it uses the version of TryParse that takes a FormatProvider and can deal with commas...

string sql14 = "select YYMM, TotalTrans  from t2 where CAST(REPLACE(TotalTrans, ',', '') as int) < 500000 and CAST(REPLACE(TotalTrans, ',', '') as int) > 1000"

SQLiteCommand cmd5 = new SQLiteCommand(sql14, sqlite_conn);
    SQLiteDataReader rdr5 = cmd5.ExecuteReader();

    while (rdr5.Read())
    {
        int TotalTranst22;
        DateTime yyyymmt22;
        if (DateTime.TryParse(rdr5["YYMM"].ToString(), out yyyymmt22) && int.TryParse(rdr5["TotalTrans"].ToString(), NumberStyles.Number, CultureInfo.CurrentCulture, out TotalTranst22))
        {
            YYMMt22.Add(yyyymmt22);
            TotalTransIrregularitiest22.Add(TotalTranst22);
        }
    }

Note that this gets you a short-term solution but you need to be asking why your schema is defined to hold a number in a string data type in the first place.

edited Dec 05 '13 at 13:32

answered Nov 29 '13 at 20:07

dazedandconfused

3,131
1
18
29

Can you elaborate? Are you getting an error or just no data? You might consider posting a sqlfiddle to make this go more smoothly. – dazedandconfused Dec 01 '13 at 23:13
@Newuser +1 Why does this solution doesn't work? This is correct. Specially 2nd paragraph! – LS_ᴅᴇᴠ Dec 03 '13 at 13:58
@LS_dev Check my updated post, still no luck. The lists dont get filled with the data at all. Im thinking of converting the lists to int data from the beginning now – Newuser Dec 03 '13 at 20:51
@dazedandconfused I get the error: No overloaded method for try parse takes 3 arguments. Also is the namespace for cultureinfo called globalization? Thanks SO much for your help – Newuser Dec 04 '13 at 23:21
1

I was going from memory and left out a parameter, code has been edited. Yes, System.Globalization is the correct namespace. – dazedandconfused Dec 05 '13 at 13:33
After one week, lots of frustration, and worry, THANK YOU !!!!!!!!!!!!!!! You may rightfully claim the bounty. A big thanks to everyone else who helped!! – Newuser Dec 05 '13 at 18:39

score 2 · Answer 2 · answered Nov 29 '13 at 19:35

2

Based on the comments and your screenshot, it seems indeed that your column is a string and the 'numbers' are already formatted with commas.

If you are allowed to modify your schema, perhaps you should consider doing that. Why use a string column if you're only storing numeric data in there?

If you're not allowed to, you could try to to a text replace on the column, removing the commas that are causing the problem. Have a look at the replace function and use it to replace commas with an empty string in order to remove them.

answered Nov 29 '13 at 19:35

Nikolaos Georgiou

2,792
1
26
32

how would I actually add a replace function? with syntax I mean. Also all data comes from lists which are initially strings, do you think trying to convert those lists to int initially would help? – Newuser Nov 29 '13 at 20:31
1

Have a look at the example that @dazedandconfused posted about using the REPLACE function. And definitely if you can convert the data in their correct data type in the database it would be great (also for performance reasons, correct sorting, etc). – Nikolaos Georgiou Nov 29 '13 at 20:35
Great I will take everything into consideration, test it then mark an answer thx – Newuser Nov 29 '13 at 21:06
This is the only sane answer. – l33t Dec 04 '13 at 14:35

score 1 · Answer 3 · edited Dec 04 '13 at 08:41

I don't have sqlite, i use sql server to try this code, perhaps can help you :

1. if TotalTrans a string

you must replace ',' to empty string

2. if TotalTrans heve decimal('.') - 2.15

you must convert to float

select 
    convert(float, replace(TotalTrans,',',''))
from
(
    select '336,604' as TotalTrans 
    union all
    select '329,548' as TotalTrans 
    union all
    select '2,734,311' as TotalTrans 
    union all
    select '2.15' as TotalTrans -- [done] with convert to float
    union all
    select '4' as TotalTrans 
    union all
    select '78' as TotalTrans 
    union all
    select '' as TotalTrans -- [done] automatic replace to 0
    union all
    select null as TotalTrans -- [done] no record
) a
where cast(replace(TotalTrans,',','') as float) < 1000

result :

--------
|2.15  |
--------
|4     |
--------
|78    |
--------
|0     |
--------

try replace `CONVERT` to `CAST` `CAST(REPLACE(TotalTrans, ',', '') as float)` — penjepitkertasku, Dec 03 '13 at 19:33

score 1 · Answer 4 · edited May 23 '17 at 11:49

1

We know that integers greater than 999 will have 4 or more digits.

SELECT YYMM, TotalTrans FROM t2 WHERE length(TotalTrans) > 3;

You may also try the BETWEEN syntax instead of two clause constraint.

 SELECT YYMM, TotalTrans FROM t2 WHERE CAST(replace(TotalTrans,',','') BETWEEN 1000 AND 500000;

This will force the conversion to occur once, which may prevent the use of any temporary tables.

As others have said, your problem lies in the text representation of that integer. You're probably aware of that, but you consider it a tradeoff; however, databases are for storing data, and your applications are for presenting it. Let C# do the number formatting for you.

How to display numeric in 3 digit grouping

And to fix your table.

UPDATE t2 SET TotalTrans = CAST(replace(TotalTrans,',','') AS INTEGER);

Remember, SQLite uses type affinity on a per field basis, so you don't need to update your schema --I recommend you do though.

All that said, if you want to know why this is happening, I suspect that it's creating a temporary table for each constraint because of all the type juggling, forcing SQLite to perform an interesting 3 table join. Your best bet is to run the EXPLAIN command on the query to see how SQLite is tackling this problem.

edited May 23 '17 at 11:49

Community

1
1

answered Dec 04 '13 at 02:20

Arik

369
2
3

Iam going to try your approach. I tried the first query but I'm getting all values < 1000 if I do totalTrans > 3.. very strange – Newuser Dec 04 '13 at 17:55
1

Newuser, have you tested your queries using the command-line tool for SQLite? http://www.sqlite.org/download.html Look under **Precompiled Binaries for Windows** / **sqlite-shell** – Arik Dec 04 '13 at 18:03
is there any sort of service where we could share screens? Im new and dont know much about how to run this – Newuser Dec 04 '13 at 18:51
1

Newuser, not that I can use at my office, but the tool is very simple. Download, extract, and copy to C:\Windows\system32. Open a command prompt, `cd` to the directory where your database is stored, enter `sqlite3 datafile`, where `datafile` is the file name of your database. From there you can run queries. Enjoy the tool, I use it daily. – Arik Dec 04 '13 at 18:57

score 1 · Answer 5 · answered Dec 05 '13 at 11:34

Your problem is here

if (DateTime.TryParse(rdr5["YYMM"].ToString(), out yyyymmt22) && int.TryParse(rdr5["TotalTrans"].ToString(), out TotalTranst22))

int.TryParse is failing. Remember you are selecting a string value and then trying to convert it to int. For your query to work you need to change it to

select YYMM, REPLACE(TotalTrans, ',', '')  from t2 where CAST(REPLACE(TotalTrans, ',', '') as int) < 500000 and CAST(REPLACE(TotalTrans, ',', '') as int) > 1000";

Otherwise in the if condition TransValue will be something like '336,604' and int.TryParse() will fail.

Hope this helps.

SQlite query not working when selecting integers in 1000s

5 Answers5