How to select the integer only in mysql

Question

I have my table named material

pcs_meter
100 m
80 m

How can I select the pcs_meter column? like:

pcs_meter
100
80

SELECT +SUBSTRING_INDEX(pcs_meter, ' ', 1) FROM material; ? — BlitZ
– BlitZ, Commented Feb 14, 2014 at 8:25
there are several ways you could achieve this... I would suggest storing the values in an array, and affecting those values through php sting functions to truncate out the measurement unit. — wribit
– wribit, Commented Feb 14, 2014 at 8:25
You should normalize your database. One of the steps says, keep values atomic. Means, put 100 in one column and m in a second column. — Daniel W.
– Daniel W., Commented Feb 14, 2014 at 8:25
@AlmaDo it has, First normal form: en.wikipedia.org/wiki/First_normal_form A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain. I'm working for a webshop company and that's why we always put currency and value in different columns. — Daniel W.
– Daniel W., Commented Feb 14, 2014 at 8:28

Alma Do · Accepted Answer · 2014-02-14 13:24:34Z

5

You can achieve that with:

SELECT CAST(pcs_meter AS UNSIGNED) FROM material

That will work since your value goes first (and MySQL will truncate non-significant symbols from the right). If your field can contain negative values, change UNSIGNED to SIGNED

edited Feb 14, 2014 at 13:24

answered Feb 14, 2014 at 8:26

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel W. Over a year ago

Must have been me, by accident?! Can you hit edit question I'll turn this into an upvote.

Alma Do Over a year ago

Doubt (it was after some certain time) - but ok, improved.

Neels · Accepted Answer · 2014-02-14 08:26:01Z

0

Use this:

SELECT SUBSTRING_INDEX(pcs_meter,' ', 1) FROM material;

answered Feb 14, 2014 at 8:26

Neels

2,5436 gold badges36 silver badges41 bronze badges

2 Comments

wribit Over a year ago

this would require every entry to have a space before the measurement unit. If that can be verified as true - than, this would work perfectly.

Neels Over a year ago

Isnt that how you will be storing the data?

Collectives™ on Stack Overflow

How to select the integer only in mysql

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related