How to select decimal record in a table

Question

question

cstephen asked Feb 19, '13

How to select decimal record in a table

If my table having decimal records say for ex:item code,item cost and i am storing the item cost values such as 1.50 ,2.50,20.50,2o,30,50,60 Etc i want to retrieve records those item having decimal values.. How will be select query..for this condition

sql-server-2008 query select

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM commented · Feb 19, 2013 at 03:27 PM

What is the data type fo the column? DECIMAL(x,y)?

0 ·

ThomasRushton ♦♦ commented · Feb 19, 2013 at 08:16 PM

What exactly is the problem? Are you looking for items stored as numbers, or stored in some other format? Are you looking for items with anything after the decimal point, or for non-zero after the decimal point?

0 ·

Answer 1 · 2013-02-19T08:36:39Z

eghetto answered Feb 19, '13

How about: ... WHERE ISNUMERIC([item cost]) = 1 or ... WHERE NOT TRY_PARSE([item cost] AS NUMERIC(19,2)) IS NULL

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2013-02-19T08:58:03Z

Vingo answered Feb 19, '13

you can convert item cost to varchar,and than use *like* or *charindex* to find those item having '.' select code,cost from table where charindex('.',convert(varchar(255),cost) ) > 0

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2013-02-19T20:09:21Z

Beandon10 answered Feb 19, '13

You could try SELECT code,cost FROM table WHERE FLOOR(cost) <> cost Depending on the table size, this might not perform great though.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Feb 19, 2013 at 08:15 PM

Probably better than @Vingo's solution, though!

0 ·

question