Re: [sqlite] trim alpha numeric string so it's numeric cast then order
PERFECT! Thanks Puneet! Mr. Puneet Kishor-2 wrote: > > > On Apr 18, 2012, at 11:20 PM, starvingpilot wrote: > >> >> >> >> Puneet Kishor-2 wrote: >>> >>> >>> On Apr 18, 2012, at 11:10 PM, starvingpilot wrote: >>> Here's a query that works sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE state like '%@ %'",theState]; <--- this yields a result: 0 sqlStatement = [NSString stringWithFormat:@"SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM stations ORDER BY power)",theState]; I get a result: 1 from that last one... "theState" in this code is a variable for that specific state I need. >>> >>> >>> I don't see a question above. It's not clear if you are asking >>> something. >>> Wrt to filtering by state, I already sent you an example for that. Hope >>> that helped. >>> >>> >>> -- >>> Puneet Kishor >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> Your query netted a result of ALL states... I'd like to search a specific >> state for example.. I'd like to find antennas in CA only and order by >> power. > > > didn't you get the following? It returns rows for only 'CA' > > SELECT state, power || ' kW' power > FROM ( > SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power > FROMstations > WHERE state = 'CA' > ORDER BYpower > ); > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33712052.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 11:20 PM, starvingpilot wrote: > > > > Puneet Kishor-2 wrote: >> >> >> On Apr 18, 2012, at 11:10 PM, starvingpilot wrote: >> >>> >>> Here's a query that works >>> >>> sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE >>> state like '%@ %'",theState]; <--- this yields a result: 0 >>> >>> sqlStatement = [NSString stringWithFormat:@"SELECT state, power || ' kW' >>> power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power >>> FROM >>> stations ORDER BY power)",theState]; >>> >>> I get a result: 1 from that last one... "theState" in this code is a >>> variable for that specific state I need. >> >> >> I don't see a question above. It's not clear if you are asking something. >> Wrt to filtering by state, I already sent you an example for that. Hope >> that helped. >> >> >> -- >> Puneet Kishor >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > Your query netted a result of ALL states... I'd like to search a specific > state for example.. I'd like to find antennas in CA only and order by > power. didn't you get the following? It returns rows for only 'CA' SELECT state, power || ' kW' power FROM ( SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROMstations WHERE state = 'CA' ORDER BYpower ); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
Puneet Kishor-2 wrote: > > > On Apr 18, 2012, at 11:10 PM, starvingpilot wrote: > >> >> Here's a query that works >> >> sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE >> state like '%@ %'",theState]; <--- this yields a result: 0 >> >> sqlStatement = [NSString stringWithFormat:@"SELECT state, power || ' kW' >> power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power >> FROM >> stations ORDER BY power)",theState]; >> >> I get a result: 1 from that last one... "theState" in this code is a >> variable for that specific state I need. > > > I don't see a question above. It's not clear if you are asking something. > Wrt to filtering by state, I already sent you an example for that. Hope > that helped. > > > -- > Puneet Kishor > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Your query netted a result of ALL states... I'd like to search a specific state for example.. I'd like to find antennas in CA only and order by power. -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33712020.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 11:10 PM, starvingpilot wrote: > > Here's a query that works > > sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE > state like '%@ %'",theState]; <--- this yields a result: 0 > > sqlStatement = [NSString stringWithFormat:@"SELECT state, power || ' kW' > power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM > stations ORDER BY power)",theState]; > > I get a result: 1 from that last one... "theState" in this code is a > variable for that specific state I need. I don't see a question above. It's not clear if you are asking something. Wrt to filtering by state, I already sent you an example for that. Hope that helped. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
Here's a query that works sqlStatement = [NSString stringWithFormat:@"SELECT * FROM stations WHERE state like '%@ %'",theState]; <--- this yields a result: 0 sqlStatement = [NSString stringWithFormat:@"SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM stations ORDER BY power)",theState]; I get a result: 1 from that last one... "theState" in this code is a variable for that specific state I need. -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33712000.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
one last thing, sorry to be such a pain... how can I search for a specific state? Right now it's listing ALL the states, I need to list from a specific state like "CA" -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33711997.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 11:04 PM, starvingpilot wrote: > > I actually did have the parenthesis there... I didnt copy and paste the last > one. It was there however and I am still having problems > Well, it is hard to help you unless you tell us exactly what problem you are having. You might want to include the actual command you are running and the actual response you are getting. Without that its just a guessing game. As I showed you with the entire transcript, the query runs just fine. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
disregard, so the query works on sqlite terminal, just not on my app. Thanks for the help, ill work on it from here... cheers! -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33711989.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
I actually did have the parenthesis there... I didnt copy and paste the last one. It was there however and I am still having problems -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33711982.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 11:01 PM, starvingpilot wrote: > > Also, I need to select from a specific state like "CA" Where is that part in > the query? SELECT state, power || ' kW' power FROM ( SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROMstations WHERE state = 'CA' ORDER BYpower ); -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
Also, I need to select from a specific state like "CA" Where is that part in the query? -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33711973.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
On Apr 18, 2012, at 10:47 PM, starvingpilot wrote: > > Thanks for the quick reply Puneet. However I had a syntax error on App as > well as when I typed that query on my sqlite browser. Here's my syntax > "SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power, > 'kW') AS 'numeric') power FROM stations ORDER BY power" > balance your parens. you are missing a closing parens after "ORDER BY power<---" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
Thanks for the quick reply Puneet. However I had a syntax error on App as well as when I typed that query on my sqlite browser. Here's my syntax "SELECT state, power || ' kW' power FROM (SELECT state, Cast(Rtrim(power, 'kW') AS 'numeric') power FROM stations ORDER BY power" table: stations fields: state, power I'm using sqlite 3 -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33711944.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trim alpha numeric string so it's numeric cast then order
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
