ISNUMERIC() function help - 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() function help

Author

Topic

sponarun
Starting Member

3 Posts

Posted - 2009-03-11 : 09:29:23

Hello,
I am having an table with the column type as
varchar.
The column value may be anything.Its include
numbers,Strings,*,#,%.etc..
But i like to gee the values which is in between
0 to 100.
So i tried as

select attribute2 from history where 
ISNUMERIC(attribute2)=1 and cast(Attribute2 
as integer) < cast('100' as integer) and 
cast(Attribute2 as integer) > cast('0' as 
integer)--attribute2<>100

Here
attribute2 - column name varchar
History - table name
Its giving the following error "cannot convert #
to int".
But i am filtering the column values by
ISNUMERIC(attribute2)=1 before casting to
integer.
Please any one guide me to solve this issue!!!
Thank you!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-11 : 09:35:47

Try

select attribute2 from history where
attribute2 not like '%[^0-9]%' and cast(Attribute2
as int) < cast('100' as int) and
cast(Attribute2 as int) > cast('0' as
int)--attribute2<>100

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 09:36:39

See http://weblogs.sqlteam.com/peterl/archive/2007/09/27/SQL-Server-2005-too-smart.aspx

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

sponarun
Starting Member

3 Posts

Posted - 2009-03-11 : 09:41:27

Hello madhivanan,
Thanks for ur reply!!!
Still i am getting the same error message.
Help me!!
Thank you!!!

quote:
Originally posted by madhivanan

Try

select attribute2 from history where
attribute2 not like '%[^0-9]%' and cast(Attribute2
as int) < cast('100' as int) and
cast(Attribute2 as int) > cast('0' as
int)--attribute2<>100

Madhivanan

Failing to plan is Planning to fail

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-11 : 10:39:12

Hi Peso.

quote:
Originally posted by Peso

See http://weblogs.sqlteam.com/peterl/archive/2007/09/27/SQL-Server-2005-too-smart.aspx

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

This seems to be working fine.
SELECT d.ColumnValue
FROM (
SELECT 'staff' AS ColumnValue UNION ALL
SELECT '234000' UNION ALL
SELECT '12d1' UNION ALL
SELECT '45e0' UNION ALL
SELECT '$123.45' UNION ALL
SELECT '$12,345'
) AS d
WHERE d.ColumnValue NOT LIKE '%[^0-9]%'
AND CAST(d.ColumnValue AS INT) <= 10000000

But when I declare it as a table and try the same it fails.
Could you please tell what would be the reason??? and how to fix this.

declare @A TABLE (YEA VARCHAR(1000) )
INSERT INTO @A SELECT 'staff' AS YEA UNION ALL
SELECT '234000' UNION ALL
SELECT '12d1' UNION ALL
SELECT '45e0' UNION ALL
SELECT '$123.45' UNION ALL
SELECT '$12,345'

select * from @A where
YEA not like '%[^0-9]%' and cast(YEA as int) <= 10000000

Karthik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 10:43:23

[code]SELECT *
FROM @a
WHERE CASE
WHEN YEA NOT LIKE '%[^0-9]%' THEN YEA
ELSE NULL
END <= 10000000[/code]

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

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-11 : 10:46:52

quote:
Originally posted by Peso

SELECT *
FROM @a
WHERE CASE
WHEN YEA NOT LIKE '%[^0-9]%' THEN YEA
ELSE NULL
END <= 10000000

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

This is simply great

Thank you very much.

Karthik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 10:48:51

Thank you.

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

sponarun
Starting Member

3 Posts

Posted - 2009-03-11 : 10:49:53

Hi Peso,
U r rite!!!
Thank u !!!!

quote:
Originally posted by Peso

SELECT *
FROM @a
WHERE CASE
WHEN YEA NOT LIKE '%[^0-9]%' THEN YEA
ELSE NULL
END <= 10000000

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources