On Apr 18, 2012, at 10:27 PM, starvingpilot wrote:
>
> Greetings! I am currently working on an iOS app and not too well versed in
> SQLite. Here's what I am trying to do.
>
> I have a table "stations" and the fields "state" populated by US States and
> "power" which is populated with strings similar to 1.0 kW, 50.0 kW 10.0 kW
> etc...
>
> so the query I need... I need something along the lines of "SELECT * FROM
> stations WHERE state like '%x%' ORDER BY ..." Here lies the issue. I need
> to trim kW from the number and turn it into an integer/double/float what
> have you so that I can order it by power lowest to highest or vice versa...
>
> So.... essentially I searching for a antennas in a specific state like "CA"
> Where x is my placeholder and I'd like to sort them by their power.
>
sqlite> CREATE TABLE t (s TEXT, p TEXT);
sqlite> INSERT INTO t VALUES ('WI', '10.3 kW');
sqlite> INSERT INTO t VALUES ('CA', '2.13 kW');
sqlite> INSERT INTO t VALUES ('MI', '31.4 kW');
sqlite> SELECT * FROM t ORDER BY p;
WI|10.3 kW
CA|2.13 kW
MI|31.4 kW
sqlite> SELECT s, p || ' kW' p FROM (SELECT s, Cast(Rtrim(p, 'kW') AS
'numeric') p FROM t ORDER BY p);
CA|2.13 kW
WI|10.3 kW
MI|31.4 kW
sqlite>
--
Puneet Kishor
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
