How to restrict decimal numbers using select clause?

Question

How to restrict decimal numbers using select clause?

cvvikram 71819

SSC Enthusiast

Points: 178
More actions
June 3, 2010 at 9:53 pm

#219674

Hi Friends,
Here is my table structure-
Table1
ColumnA ColumnB ColumnC
11111 -1 AAAAA
11112 -2 BBBBBB
11113 -3.17 CCCCC
11114 -1 DDDDD
11115 -0.2 EEEEEE
Can you please let me know how to restrict the rows having the decimal values in the columnB in the select clause of the above table?
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Are you saying that if ColumnB has a decimal portion, that you don't want it?

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

check if below code works for you..

declare @test-2 table(Col1 int,Col2 decimal(5,2),Col3 varchar(100))

insert into @test-2 select 11111,-1,'AAAAA'

union all

select 11112,-2.4567,'BBBBBB'

union all

select 11113,-3.17,'CCCCC'

union all

select 11114,-1,'DDDDD'

union all

select 11115,-0.2,'EEEEEE'

select Col1,left(col2,charindex('.',Col2)-1),col3 from @test-2

cvvikram -- since you are a newbie to this forum, i have prepared sample DDL and DML for you, you will have to send this kind of readily consumable script so that you get the accurate and faster responses. check the first link in Waynes signature for future posts.

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

arun.sas SSChampion Points: 11831 More actions · Answer 3

how to restrict the rows having the decimal values in the columnB in the select clause of the above table?

select * from @test-2

where round(col2,0)- col2 = 0.00

cvvikram 71819 SSC Enthusiast Points: 178 More actions · Answer 4

arun.sas (6/3/2010)
how to restrict the rows having the decimal values in the columnB in the select clause of the above table?
select * from @test-2
where round(col2,0)- col2 = 0.00

Worked perfectly!

Thanks very much friend.