trim alpha numeric string so it's numeric cast then order

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

trim alpha numeric string so it's numeric cast then order

starvingpilot
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!
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

Puneet Kishor-2

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

starvingpilot
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
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

Puneet Kishor-2

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

starvingpilot
In reply to this post by Puneet Kishor-2
Also, I need to select from a specific state like "CA"  Where is that part in the query?
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

Puneet Kishor-2

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
        FROM stations
        WHERE state = 'CA'
        ORDER BY power
);


--
Puneet Kishor
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

starvingpilot
In reply to this post by Puneet Kishor-2
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
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

starvingpilot
In reply to this post by Puneet Kishor-2
disregard, so the query works on sqlite terminal, just not on my app.  Thanks for the help, ill work on it from here... cheers!
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

Puneet Kishor-2
In reply to this post by starvingpilot

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

starvingpilot
In reply to this post by Puneet Kishor-2
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"
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

starvingpilot
In reply to this post by Puneet Kishor-2
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.
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

Puneet Kishor-2

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

starvingpilot

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.
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

Puneet Kishor-2

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
>> [hidden email]
>> 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
        FROM stations
        WHERE state = 'CA'
        ORDER BY power
);



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: trim alpha numeric string so it's numeric cast then order

starvingpilot
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
        FROM stations
        WHERE state = 'CA'
        ORDER BY power
);



_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users