question

cstephen avatar image
cstephen asked

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-2008queryselect
2 comments
10 |1200

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

What is the data type fo the column? DECIMAL(x,y)?
0 Likes 0 ·
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 Likes 0 ·
eghetto avatar image
eghetto answered
How about: ... WHERE ISNUMERIC([item cost]) = 1 or ... WHERE NOT TRY_PARSE([item cost] AS NUMERIC(19,2)) IS NULL
10 |1200

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

Vingo avatar image
Vingo answered
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

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

Beandon10 avatar image
Beandon10 answered
You could try SELECT code,cost FROM table WHERE FLOOR(cost) <> cost Depending on the table size, this might not perform great though.
1 comment
10 |1200

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

Probably better than @Vingo's solution, though!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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