Sql query to get decimal values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sql query to get decimal values

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-05-09 : 13:56:23

Hello,

I have one table with Column Dept, DeptID , DeptLoc.

In DeptID Column we have so many different values like 1000,1001, 1002, 10.1000,10.1001,20.1001 etc

My requirement is i need to filter only decimal values 10.1000,10.1001,20.1001 i dont want other values(1000,1001,1002 etc)

How can i acheive this using query?

Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-09 : 14:03:59

[code]SELECT *
FROM Table
WHERE DeptID LIKE '%[^0-9]%'[/code]

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-05-09 : 14:11:36

With that query i am getting data for all the DeptID values but i just want to get the data where DeptId is like 10.1000,10.1001,20.1001

Thank you.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-09 : 14:13:42

Try this

[CODE]
SELECT DeptID from TABLE where (DeptID - floor(DeptID)) > 0
[/CODE]

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-05-09 : 14:20:35

Hi MuMu88,

I am getting below error while i am executing that query.

Error converting data type varchar to float.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-09 : 14:31:13

quote:
Originally posted by archana23

With that query i am getting data for all the DeptID values but i just want to get the data where DeptId is like 10.1000,10.1001,20.1001

Thank you.

Left try to clarify. What is the data type of DeptID? What version of SQL Server are you using?

Can you post sample data in a consumable format and expected output?
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

DECLARE @Foo TABLE (Val VARCHAR(30))

INSERT @Foo
VALUES
('12324'), 
('123.4')

SELECT *
FROM @Foo
WHERE Val LIKE '%[^0-9]%'

(2 row(s) affected)
Val
------------------------------
123.4

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-09 : 14:36:41

you can try this but Lamprey's solution is better
[CODE]
SELECT DeptID from TABLE where (CAST(DeptID as Decimal(10,5)) - floor(DeptID as Decimal(10,5))) = 0
[/CODE]

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-05-09 : 14:37:13

Hi Lamprey,

Datatype of DeptId is char(50) and i am using sqlserver 2008 version.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-09 : 14:52:28

Here is a slightly better solution:
[CODE]
SELECT * from TABLE where (LEN(DepID) - LEN( REPLACE(DepID, '.', ''))) = 0;
[/CODE]

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-05-09 : 14:55:58

quote:
Originally posted by MuMu88

Here is a slightly better solution:
[CODE]
SELECT * from TABLE where (LEN(DepID) - LEN( REPLACE(DepID, '.', ''))) = 0;
[/CODE]

Thanks MuMu88,

It worked for me.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-09 : 15:10:57

quote:
Originally posted by archana23

Hi Lamprey,

Datatype of DeptId is char(50) and i am using sqlserver 2008 version.

Excellent!

Now.. Sample data?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-09 : 15:19:55

Since you are using the CHAR datatype (which I'd suggest you change), you need to trim off the trailing spaces.

DECLARE @Foo TABLE (Val CHAR(50))

INSERT @Foo
VALUES
('12324'), 
('123.4')

SELECT *
FROM @Foo
WHERE RTRIM(VAL) LIKE '%[^0-9]%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources