Will someone be able to explain this weird outcome...

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

Will someone be able to explain this weird outcome...

jose isaias cabrera

Greetings!

select 7,915 - 5,021;

displays this result:

7|910|21

I was really looking to have 2,894 returned, but instead I received the
above.  Then, I added quotes,

sqlite> select "7,915" - "5,021";
2
sqlite> select '7,915' - '5,021';
2

I was able to figure out that comma's are more important than just a 1000
number delemeter, so I received the right answer by taking the commas out:

sqlite> select 7915 - 5021;
2894

But, would someone explain the result of 2?  Sorry for this child-like
question, but I can't find the how the result of 2 came to be displayed.
Thanks.

josé
Any thoughts?

_______________________________________________
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: Will someone be able to explain this weird outcome...

Tony Papadimitriou
Apparently, in trying to make numbers out of strings, it is interpreted as
7 - 5 = 2 and the part after the comma is truncated.

-----Original Message-----
From: jose isaias cabrera

sqlite> select "7,915" - "5,021";
2

But, would someone explain the result of 2?  Sorry for this child-like
question, but I can't find the how the result of 2 came to be displayed.

_______________________________________________
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: Will someone be able to explain this weird outcome...

Jean-Christophe Deschamps-3
In reply to this post by jose isaias cabrera
select 7,915 - 5,021
is:
select 7,  915 - 5,  021
giving
7          910        21
just like
select 'a', 915 - 5, 'b'

_______________________________________________
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: Will someone be able to explain this weird outcome...

Simon Slavin-3
In reply to this post by Tony Papadimitriou

On 10 Oct 2014, at 9:27pm, [hidden email] wrote:

> sqlite> select "7,915" - "5,021";
> 2
>
> But, would someone explain the result of 2?  Sorry for this child-like
> question, but I can't find the how the result of 2 came to be displayed.

7 - 5 = 2

Simon.
_______________________________________________
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: Will someone be able to explain this weird outcome...

Roger Binns
In reply to this post by jose isaias cabrera
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/10/2014 01:18 PM, jose isaias cabrera wrote:
> I was able to figure out that comma's are more important than just
> a 1000 number delemeter, so I received the right answer by taking
> the commas out:

To help avoid this in the future, be aware that how developers deal
with numbers and how users experience them are very different.

People using your apps expect to see numbers in the normal way for
them.  For example thousands separators are useful, but note that some
locales group differently (eg around ten thousands).  Some use dots
not commas, and others the other way around.  Some use dots for the
decimal point and others use a comma.  Some don't use Arabic numerals
(0, 1, 2, 3 etc)

  https://en.wikipedia.org/wiki/Decimal_mark#Digit_grouping

Fortunately the operating system and programming environment provide
ways to output numbers (and dates, currency etc) in the most
appropriate way for the user.

Widespread programming languages wouldn't work very well if numbers
weren't consistently formatted (eg what happens if a developer in a
different locale runs the code).  Reflecting their origins, they
almost always only accept the anglo-centric integer notation of no
grouping and a dot as the decimal point.  SQLite uses SQL which does
the same.

For you that means separating out text that you are using with SQLite,
versus text that is shown/accepted from the user.  If you mix them
together you'll end up with unexpected behaviour, crashes, wrong
results etc.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlQ4dEsACgkQmOOfHg372QS17gCdGr31RcjBKe7ncvHbR8yAyoCW
dkAAoMZyiAzNIsVkirunvVWCh5ADspPq
=fCjG
-----END PGP SIGNATURE-----
_______________________________________________
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: Will someone be able to explain this weird outcome...

Tony Papadimitriou
In reply to this post by Simon Slavin-3
Well, OK, but you attributed the quote to the wrong person. :)

-----Original Message-----
From: Simon Slavin
Sent: Saturday, October 11, 2014 2:38 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Will someone be able to explain this weird outcome...


On 10 Oct 2014, at 9:27pm, [hidden email] wrote:

> sqlite> select "7,915" - "5,021";
> 2
>
> But, would someone explain the result of 2?  Sorry for this child-like
> question, but I can't find the how the result of 2 came to be displayed.

7 - 5 = 2

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
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: Will someone be able to explain this weird outcome...

jose isaias cabrera
In reply to this post by Roger Binns

"Roger Binns" wrote...


> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 10/10/2014 01:18 PM, jose isaias cabrera wrote:
>> I was able to figure out that comma's are more important than just
>> a 1000 number delemeter, so I received the right answer by taking
>> the commas out:
>
> To help avoid this in the future, be aware that how developers deal
> with numbers and how users experience them are very different.
>
> People using your apps expect to see numbers in the normal way for
> them.  For example thousands separators are useful, but note that some
> locales group differently (eg around ten thousands).  Some use dots
> not commas, and others the other way around.  Some use dots for the
> decimal point and others use a comma.  Some don't use Arabic numerals
> (0, 1, 2, 3 etc)
>
>  https://en.wikipedia.org/wiki/Decimal_mark#Digit_grouping
>
> Fortunately the operating system and programming environment provide
> ways to output numbers (and dates, currency etc) in the most
> appropriate way for the user.
>
> Widespread programming languages wouldn't work very well if numbers
> weren't consistently formatted (eg what happens if a developer in a
> different locale runs the code).  Reflecting their origins, they
> almost always only accept the anglo-centric integer notation of no
> grouping and a dot as the decimal point.  SQLite uses SQL which does
> the same.
>
> For you that means separating out text that you are using with SQLite,
> versus text that is shown/accepted from the user.  If you mix them
> together you'll end up with unexpected behaviour, crashes, wrong
> results etc.

thanks, Roger.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users