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
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
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
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
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
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
[sqlite] trim alpha numeric string so it's numeric cast then order
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. Any help is greatly appreciated! -- View this message in context: http://old.nabble.com/trim-alpha-numeric-string-so-it%27s-numeric-cast-then-order-tp33711898p33711898.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
