Mysql select query not retrieving decimal value

Question

I have sizes table with decimal and non-decimal value so I want to fetch data as per requirement and I have written mysql query for this, but getting empty result even data is present.

Note:I don't want to use LIKE clause

Table:

id      size
1       17X23
2       28.3X39.8 
3       25X39.8
4       28X36

Mysql query:

Select * from sizes where size='28.3X39.8'; // X is uppercase 'X' alphabet I have used to mention width X height

Output: empty result

@Claus Bönnhoff I am storing widthXheight values which contain decimal and non-decimal value and to represent multiplication symbol I am using X — user_1234
– user_1234, Commented Feb 16, 2021 at 16:31
As presented here, your code works as expected: sqlfiddle.com/#!9/dadecc/1 — PM 77-1
– PM 77-1, Commented Feb 16, 2021 at 16:31
I guess he does not even now which datatype his columns are. Elsewhere he would show us his table description — Thallius
– Thallius, Commented Feb 16, 2021 at 17:34

ScaisEdge · Accepted Answer · 2021-02-16 16:35:49Z

1

could be you have some hidden space

select Select * from sizes where trim(size)='28.3X39.8';

or

select Select * from sizes where trim(replace(size,' ',''))='28.3X39.8';

or

select Select * from sizes where upper(trim(replace(size,' ','')))=uppper(trim('28.3X39.8'));

edited Feb 16, 2021 at 16:35

answered Feb 16, 2021 at 16:31

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

5 Comments

user_1234 Over a year ago

I tried both queries , still didn't get any output

ScaisEdge Over a year ago

answer updated with another suggestion too.. anyway which data type is the column size??

user_1234 Over a year ago

varchar data type used, I tried all suggested queries but I am surprised why none of then working

user_1234 Over a year ago

how to do that?

ScaisEdge Over a year ago

exit and reenter in your ide or db console first .. restart your db service . or restart you server .. .

t4r4z0k · Accepted Answer · 2021-02-16 16:34:44Z

0

You can try this:

select * from sizes where trim(upper(size))='28.3X39.8';

answered Feb 16, 2021 at 16:34

t4r4z0k

261 silver badge4 bronze badges

Collectives™ on Stack Overflow

Mysql select query not retrieving decimal value

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related