store numeric numbers with plus minus signs

[[e-mail]]

I didn't find this topic in the discussion history ... wonder what's the best practice in MySQL to store numeric numbers with plus minus signs (+/-), times sign such as in 2.328x10-15, such that they can be safely stored, easily handled/recognized by most current programs in perl/php/etc and
shown in modern web browsers.

My problem came from a practice developing a MySQL database since 20 years ago, when such numeric numbers are copied/pasted into MySQL via web forms and displayed well on web when retrieved back then. However in the following years these old data were started to be shown as with black
diamond squares or other types of partial-garbaged number.

Over 10+ years ago when these strange character showed up I can doggle for different character coding methods in a browser to view them. But nowadays all browsers are made "smart" as no longer
to provide these user options.

I understand this came along with different/evolving char coding methods; and the data we curated into the database over different times in history were with changing character (or language) settings. I wonder is there a good solution to bring them all to a current character safe environment, and kept safe for the future?

j

[Jerry Stuckle]

Post by ***@gmail.com
I didn't find this topic in the discussion history ... wonder what's the best practice in MySQL to store numeric numbers with plus minus signs (+/-), times sign such as in 2.328x10-15, such that they can be safely stored, easily handled/recognized by most current programs in perl/php/etc and
shown in modern web browsers.
My problem came from a practice developing a MySQL database since 20 years ago, when such numeric numbers are copied/pasted into MySQL via web forms and displayed well on web when retrieved back then. However in the following years these old data were started to be shown as with black
diamond squares or other types of partial-garbaged number.
Over 10+ years ago when these strange character showed up I can doggle for different character coding methods in a browser to view them. But nowadays all browsers are made "smart" as no longer
to provide these user options.
I understand this came along with different/evolving char coding methods; and the data we curated into the database over different times in history were with changing character (or language) settings. I wonder is there a good solution to bring them all to a current character safe environment, and kept safe for the future?
j

The SQL standard states a plus sign is optional whereas a minus sign is
required for negative numbers. Addtionally, the times sign is not
allowed. Instead, "E" is used, as in 2.328E-15.

This is standard in many programming languages, also.

And of course you store numeric values in a numeric type column.

Also, NEVER copy/paste from a web form when working with ANY database.
ALWAYS validate the information before inserting to prevent SQL
injection exploits.

[[e-mail]]

Post by Jerry Stuckle

Post by ***@gmail.com
I didn't find this topic in the discussion history ... wonder what's the best practice in MySQL to store numeric numbers with plus minus signs (+/-), times sign such as in 2.328x10-15, such that they can be safely stored, easily handled/recognized by most current programs in perl/php/etc and
shown in modern web browsers.
My problem came from a practice developing a MySQL database since 20 years ago, when such numeric numbers are copied/pasted into MySQL via web forms and displayed well on web when retrieved back then. However in the following years these old data were started to be shown as with black
diamond squares or other types of partial-garbaged number.
Over 10+ years ago when these strange character showed up I can doggle for different character coding methods in a browser to view them. But nowadays all browsers are made "smart" as no longer
to provide these user options.
I understand this came along with different/evolving char coding methods; and the data we curated into the database over different times in history were with changing character (or language) settings. I wonder is there a good solution to bring them all to a current character safe environment, and kept safe for the future?
j

The SQL standard states a plus sign is optional whereas a minus sign is
required for negative numbers. Addtionally, the times sign is not
allowed. Instead, "E" is used, as in 2.328E-15.
This is standard in many programming languages, also.
And of course you store numeric values in a numeric type column.
Also, NEVER copy/paste from a web form when working with ANY database.
ALWAYS validate the information before inserting to prevent SQL
injection exploits.
--
==================
Remove the "x"'s from my email address
Jerry Stuckle
==================

Thank you Jerry for the heads up. Perhaps we should consider a catch on web forms for "illegal" characters and suggest fixes. Otherwise it was difficult to expect all to practice the same way.

[[e-mail]]

Post by Jerry Stuckle

Post by ***@gmail.com
I didn't find this topic in the discussion history ... wonder what's the best practice in MySQL to store numeric numbers with plus minus signs (+/-), times sign such as in 2.328x10-15, such that they can be safely stored, easily handled/recognized by most current programs in perl/php/etc and
shown in modern web browsers.
My problem came from a practice developing a MySQL database since 20 years ago, when such numeric numbers are copied/pasted into MySQL via web forms and displayed well on web when retrieved back then. However in the following years these old data were started to be shown as with black
diamond squares or other types of partial-garbaged number.
Over 10+ years ago when these strange character showed up I can doggle for different character coding methods in a browser to view them. But nowadays all browsers are made "smart" as no longer
to provide these user options.
I understand this came along with different/evolving char coding methods; and the data we curated into the database over different times in history were with changing character (or language) settings. I wonder is there a good solution to bring them all to a current character safe environment, and kept safe for the future?
j

The SQL standard states a plus sign is optional whereas a minus sign is
required for negative numbers. Addtionally, the times sign is not
allowed. Instead, "E" is used, as in 2.328E-15.
This is standard in many programming languages, also.
And of course you store numeric values in a numeric type column.
Also, NEVER copy/paste from a web form when working with ANY database.
ALWAYS validate the information before inserting to prevent SQL
injection exploits.
--
==================
Remove the "x"'s from my email address
Jerry Stuckle
==================

Thank you Jerry for the heads up. Perhaps we should consider a catch on web forms for "illegal" characters and suggest fixes. Otherwise it was difficult to expect all to practice the same way.

By the way what would be the way to store numbers with plus/minus sign (� or ± or U+00B1 or ±). THANKS!

[Jerry Stuckle]

Post by ***@gmail.com

Post by Jerry Stuckle

Post by ***@gmail.com
I didn't find this topic in the discussion history ... wonder what's the best practice in MySQL to store numeric numbers with plus minus signs (+/-), times sign such as in 2.328x10-15, such that they can be safely stored, easily handled/recognized by most current programs in perl/php/etc and
shown in modern web browsers.
My problem came from a practice developing a MySQL database since 20 years ago, when such numeric numbers are copied/pasted into MySQL via web forms and displayed well on web when retrieved back then. However in the following years these old data were started to be shown as with black
diamond squares or other types of partial-garbaged number.
Over 10+ years ago when these strange character showed up I can doggle for different character coding methods in a browser to view them. But nowadays all browsers are made "smart" as no longer
to provide these user options.
I understand this came along with different/evolving char coding methods; and the data we curated into the database over different times in history were with changing character (or language) settings. I wonder is there a good solution to bring them all to a current character safe environment, and kept safe for the future?
j

The SQL standard states a plus sign is optional whereas a minus sign is
required for negative numbers. Addtionally, the times sign is not
allowed. Instead, "E" is used, as in 2.328E-15.
This is standard in many programming languages, also.
And of course you store numeric values in a numeric type column.
Also, NEVER copy/paste from a web form when working with ANY database.
ALWAYS validate the information before inserting to prevent SQL
injection exploits.
--
==================
Remove the "x"'s from my email address
Jerry Stuckle
==================

Thank you Jerry for the heads up. Perhaps we should consider a catch on web forms for "illegal" characters and suggest fixes. Otherwise it was difficult to expect all to practice the same way.

By the way what would be the way to store numbers with plus/minus sign (� or ± or U+00B1 or ±). THANKS!

You need to ensure your database uses a charset that accepts those
characters.

[J.O. Aho]

Post by ***@gmail.com
By the way what would be the way to store numbers with plus/minus sign (� or ± or U+00B1 or ±). THANKS!

You need to see to using the right charset and that it's the same on the
front end as in database.

[[e-mail]]

Post by J.O. Aho

Post by ***@gmail.com
By the way what would be the way to store numbers with plus/minus sign (� or ± or U+00B1 or ±). THANKS!

You need to see to using the right charset and that it's the same on the
front end as in database.
--
//Aho

Thank you Jerry and Aho! It's much appreciated for you to point me to a right direction.

[Jerry Stuckle]

Post by ***@gmail.com

Post by Jerry Stuckle

Post by ***@gmail.com
I didn't find this topic in the discussion history ... wonder what's the best practice in MySQL to store numeric numbers with plus minus signs (+/-), times sign such as in 2.328x10-15, such that they can be safely stored, easily handled/recognized by most current programs in perl/php/etc and
shown in modern web browsers.
My problem came from a practice developing a MySQL database since 20 years ago, when such numeric numbers are copied/pasted into MySQL via web forms and displayed well on web when retrieved back then. However in the following years these old data were started to be shown as with black
diamond squares or other types of partial-garbaged number.
Over 10+ years ago when these strange character showed up I can doggle for different character coding methods in a browser to view them. But nowadays all browsers are made "smart" as no longer
to provide these user options.
I understand this came along with different/evolving char coding methods; and the data we curated into the database over different times in history were with changing character (or language) settings. I wonder is there a good solution to bring them all to a current character safe environment, and kept safe for the future?
j

The SQL standard states a plus sign is optional whereas a minus sign is
required for negative numbers. Addtionally, the times sign is not
allowed. Instead, "E" is used, as in 2.328E-15.
This is standard in many programming languages, also.
And of course you store numeric values in a numeric type column.
Also, NEVER copy/paste from a web form when working with ANY database.
ALWAYS validate the information before inserting to prevent SQL
injection exploits.
--
==================
Remove the "x"'s from my email address
Jerry Stuckle
==================

Thank you Jerry for the heads up. Perhaps we should consider a catch on web forms for "illegal" characters and suggest fixes. Otherwise it was difficult to expect all to practice the same way.

You should ALWAYS validate ALL data before allowing it to be used in a
SQL statement. Failure to do so is a major security exposure.

And you can set rules so that everyone does it the same way.