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 =

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 '%@

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

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 || '

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')

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:

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

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

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

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 FROM

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.

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

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

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

[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

Re: [sqlite] query help with inline conversion of hex data

On Wed, Apr 18, 2012 at 6:38 PM, Jim Sanders wrote: > > It's disappointing there isn't a simple "unhex()" function to do the > reverse of the already existing function. > Code to implement such a function is contained in the SQLite test suite. See it here:

Re: [sqlite] query help with inline conversion of hex data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/04/12 15:38, Jim Sanders wrote: > It's disappointing there isn't a simple "unhex()" function to do the > reverse of the already existing function. That would be a very > elegant solution to this issue, as I am so close. unhex isn't anywhere

Re: [sqlite] query help with inline conversion of hex data

Well yes I agree the stored format isn't ideal, but unfortunately I have no control over that. These are hard drive serial numbers and that is the format that Microsoft WMI spits them out in. It's possible that the Spiceworks application could be modified to decode those hex strings it gets

Re: [sqlite] query help with inline conversion of hex data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/04/12 14:39, Jim Sanders wrote: > I found that, but I need essentially the reverse of that. The data > stored in SQL is 3061626364 and I need to convert it back to 0abcd > (from your example) The correct fix is to get them to stop storing

Re: [sqlite] query help with inline conversion of hex data

I found that, but I need essentially the reverse of that. The data stored in SQL is 3061626364 and I need to convert it back to 0abcd (from your example) On Wed, Apr 18, 2012 at 5:35 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On

Re: [sqlite] query help with inline conversion of hex data

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/04/12 14:30, Jim Sanders wrote: > But I can't figure out a way do this dynamically for all rows. There is a builtin hex() function. sqlite> create table foo(bar); insert into foo values('0abcd'); sqlite> select hex(bar) from foo;

[sqlite] query help with inline conversion of hex data

I am trying to write a query to a SQLite database (Spiceworks) in which I need to transform some data as part of the SELECT statement. The query I *want* to use looks like this (includes some pseudo code): SELECT d.name, d.current_user, d.device_type, d.model, d.operating_system,

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entityframework

On Wed, Apr 18, 2012 at 04:09:21PM +, Black, Michael (IS) scratched on the wall: > Good to know... > > But why would his ADO be giving back NULL? > > Unless it has bastardized the sqlite interface? That would be my guess. > And I guess that means that if you add a row with a default

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entityframework

Good to know... But why would his ADO be giving back NULL? Unless it has bastardized the sqlite interface? And I guess that means that if you add a row with a default value then you should do an "update" afterwords to ensure all the rows are indeed populated. That may make his ADO happy.

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entity framework

On 18 Apr 2012, at 1:54pm, Steffen Mangold wrote: >> I think your problem is with the ado provider. Perhaps it makes the >> assumption that every value in a BOOLEAN column must be a BOOLEAN. This is >> not true under SQLite: you can have any value in a BOOLEAN

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entityframework

On Wed, Apr 18, 2012 at 01:20:59PM +, Black, Michael (IS) scratched on the wall: > default values are during INSERT...not SELECT. > Unless the table has had a column added with ALTER TABLE. In that case, the column doesn't exist in the on-disk record of the row. The SELECT knows it

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entityframework

default values are during INSERT...not SELECT. I suppose it's possible Maestro is messing it up. You just need to do an "update mytable set IsReplaced=0 where IsReplaced is null;" You can do that from the shell. Hopefully that makes your ADO happy. If you want to test the shell then

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entityframework

> > Have you checked your table afterwords to ensure you don't have any nulls in > IsReplaced? > > select count(IsReplaced) from mytable where IsReplaced is null; > > I tested and the alter table does fill with default values for me. At least > from the sqlite shell. > > Does this work for you?

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entityframework

Have you checked your table afterwords to ensure you don't have any nulls in IsReplaced? select count(IsReplaced) from mytable where IsReplaced is null; I tested and the alter table does fill with default values for me. At least from the sqlite shell. Does this work for you? Are you

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entity framework

> > I think your problem is with the ado provider. Perhaps it makes the > assumption that every value in a BOOLEAN column must be a BOOLEAN. This is > not true under SQLite: you can have any value in a BOOLEAN column, even TEXT. > Hi Simon, Yes correct because if you alter a table with a new

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entity framework

On 18 Apr 2012, at 1:44pm, Steffen Mangold wrote: > I have an existing table filled with data and want to add a new column like > "IsReplaced BOOLEAN NOT NULL DEFAULT 0" > All worked fine but if I now try to read a data row I gat an exception from > the sqlite

Re: [sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entity framework

> > Hi guys, > > i have a little problem with BOOLEAN data column. > > I have an existing table filled with data and want to add a new column like > "IsReplaced BOOLEAN NOT NULL DEFAULT 0" > All worked fine but if I now try to read a data row I gat an exception from > the sqlite ado provider

[sqlite] "DEFAULT BOOLEAN NOT NULL" not working with entity framework

Hi guys, i have a little problem with BOOLEAN data column. I have an existing table filled with data and want to add a new column like "IsReplaced BOOLEAN NOT NULL DEFAULT 0" All worked fine but if I now try to read a data row I gat an exception from the sqlite ado provider that he can not