IsNumeric() and decimals. - SQL Server Forums

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

IsNumeric() and decimals.

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2010-03-05 : 14:01:51

I'm trying to get results in a query where a field contains only one decimal point. It's an odd implementation, so I won't even get into that... what I'm trying to do is return all results that only have one decimal point.

Example:


Id       decimal
1          1.0
2          1.1 
3          1.1.0
4          1.1.1
5          2.2.2.2

select * 
from   table
where  ISNUMERIC(decimal) <> 0

In theory, this should work, but I want to get your opinions on this... is ISNUMERIC the right way to do this?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-05 : 14:16:24

Try this? Making use of Parsename function

declare @t table 
(Id int       ,[decimal] varchar(10))
insert @t
select 1,          '1.0'
union all select 2,          '1.1 '
union all select 3,          '1.1.4'
union all select 4,          '1.1.1'
union all select 5,          '2.2.2.2'

Query

select * from @t
where parsename([decimal],3) is null

EDIT : However, if you have any records with no decimal at all, it will select that record too.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2010-03-05 : 14:43:31

select * from @t
where len([col]) - len(replace([col], '.', '')) = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-05 : 15:05:22

Keep is short, keep it simple

SELECT	*
FROM	@t
WHERE	[decimal] NOT LIKE '%.%.%'
	AND [decimal] LIKE '%.%'

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources