[SOLVED] Tricky SQLite sort

[bobonwhidbey]

My DB has a score field that sometimes is a real number, and sometimes in a PCT, with a % sign. I want to sort this column so that all the Pct scores stay together and all the real numbers stay together. Like this:
   
   0.12
  12.45
   8.05%
  14.50%
 100.00%

I was hoping to use something like the following:

Code: Pascal  [Select][+][-]

1. 'SELECT * from DataTable Order By Case StrScan(Score,''%'') WHEN ''0'' then 1 ELSE ''0'' END , Cast(Score as Real) '

but there is no such code as StrScan. I've also tried StrPos, but doesn't exist either. How can I accomplish this sort?

[sfeinst]

This is off the top of my head so it has not been tested.  But replace your StrScan with
substr(Score, Length(Score), 1) and see if that works.

Overall, though, you may want to consider why your database uses mixed data in a column and if that is a good design.  Also, since % is in the field, I'm guessing it is a varchar of some type where a numeric field might better.

Percents can be stored as real to save you issues.  Of course, if you inherit the database, not much you can do.

[Thaddy]

I guess, since it is obviously allowed to store both species (bad design) you store it as a string. In that case why not use

Code: MySQL  [Select][+][-]

1.  LIKE '*'%''%

dunno but something like that should work.

[bobonwhidbey]

LIKE will work to select just those records with/without a "%" in the Score field, but it won't work to differentiate them on a sort in the same column

[paweld]

Code: MySQL  [Select][+][-]

1. SELECT * from DataTable Order By Case WHEN length(replace(score, '%', ''))<length(score) then 1 ELSE 0 END , Cast(Score as Real)

[shobits1]

This will work (Tested on MS-Access):

Code: MySQL  [Select][+][-]

1. SELECT *
2. FROM DataTable
3. ORDER BY IIf(Right([Score],1)="%",1,0), IIf(Right([Score],1)="%",CDbl(Replace([Score],"%","")),CDbl([Score]));
4.  

keep in mind this assumes that % will be at the most right of the score with no trilling spaces other wise you should use InStr instead of Right to check if % in score.

[bobonwhidbey]

I don't think any of those functions exist in SQLite. Certainly one of the downside for using SQLite.

The SQLite documentation talks about an InStr(x,y) function, but that doesn't seem to work within Order By. I tried this to no avail:

Code: Pascal  [Select][+][-]

1. 'SELECT * from DataTable Order By Case InStr(Score,''%'') When 0 then 0 ELSE 1 END , Score'

[shobits1]

Sorry didn't pay attention to Post's Tilte (SQLite) 

anyway this will work (tested) http://sqliteonline.com/#fiddle-57ad04f9f393bf53e02a54f567fe058c418d6d161bf7f45475

Code: MySQL  [Select][+][-]

1. SELECT *
2. FROM DataTable
3. ORDER BY instr(Score, "%")<>0, Cast( replace(Score, "%", "") AS REAL)
4.  

[bobonwhidbey]

Very nice shobits1

When I run your code in my Laz app, I get an error message: no such function :  instr

BUT when I run your code in "DB Browser for SQLite", version 3.8.0, it works perfectly. That app uses SQLite version 3.9.2

It seems I need a new version of SQLite for my Laz 1.6.  So I downloaded the latest version of SQLite3.dll, put in in my app's folder, and ALL IS WELL.

THANK YOU.

[shobits1]

According to the SQLite site instr function was added back in 2012 (v3.7.15) so you are using a very old version,, as to what you need to do is to download the last dll version and copy it to the lazarus directory and to you application project (where the exe file).

Also, you may need to upgrade your database file, if you don't have any important data then I advice to create new database with the latest sqlite.

useful links:
https://www.sqlite.org/download.html
http://www.sqlite.org/changes.html
https://www.sqlite.org/cli.html
http://stackoverflow.com/questions/3424156/upgrade-sqlite-database-from-one-version-to-another