Compare an NVarchar Column to a Number

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

SQL Server 2005 Forums

Transact-SQL (2005)

Compare an NVarchar Column to a Number

Author

Topic

dbechberger
Starting Member

2 Posts

Posted - 2009-01-09 : 10:19:16

I have a table that contains data in an nvarchar column such as:

Column A
5
5
2
3
TBD

What I would like to be able to do is search on Column A>2 and Column A<5. The problem is that the TBD is in there and it throws an error trying to convert it to a numeric. I tried the code below with the same error. How can I do this?

SELECT a.ID FROM (select * FROM tableA WHERE isnumeric(ColumnA)=1) as a WHERE a.ColumnA>=1.0 AND a.ColumnA<=5.0

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-01-09 : 10:24:44

declare @a table (
columna Nvarchar(10)
)

insert into @a
select 5 union all
select 5 union all
select 2 union all
select 3 union all
select 1 union all
select 4.5 union all
select 5.1 union all
select 7.0 union all
select 8.0 union all
select 9.0 union all
select 'TBD'

select *
from @a
where isnumeric(columna) = 1
and columna between 1.0 and 5.0

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 10:31:54

try this

declare @tab1 table ( a nvarchar(50))
insert into @tab1
select '5' union all
select '5' union all
select '2' union all
select '3' union all
select 'tbd'

select * from( select a from @tab1 where isnumeric(a)= 1 ) p where p.a > =1 and p.a < = 5

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-09 : 10:34:12

quote:
Originally posted by dbechberger

I have a table that contains data in an nvarchar column such as:

Column A
5
5
2
3
TBD

What I would like to be able to do is search on Column A>2 and Column A<5. The problem is that the TBD is in there and it throws an error trying to convert it to a numeric. I tried the code below with the same error. How can I do this?

SELECT a.ID FROM (select * FROM tableA WHERE isnumeric(ColumnA)=1) as a WHERE a.ColumnA>=1.0 AND a.ColumnA<=5.0

Perhaps try using an IN clause?

SELECT ID
FROM TABLEA
WHERE ID
IN (SELECT ID FROM TABLEA
WHERE ColumnA not like 'TBD'
AND ColumnA BETWEEN 1.0 AND 5.0)

I got a chance to test this and it works. It returns all ID's where Column A value is between 1.0 and 5.0 inclusive

r&r

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 10:37:10

quote:
Originally posted by jhocutt

declare @a table (
columna Nvarchar(10)
)

insert into @a
select 5 union all
select 5 union all
select 2 union all
select 3 union all
select 1 union all
select 4.5 union all
select 5.1 union all
select 7.0 union all
select 8.0 union all
select 9.0 union all
select 'TBD'

select *
from @a
where isnumeric(columna) = 1
and columna between 1.0 and 5.0

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

hi,

Put single quotes while inserting numbers into @a table otherwise it will throw error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 11:35:02

quote:
Originally posted by raky

quote:
Originally posted by jhocutt

declare @a table (
columna Nvarchar(10)
)

insert into @a
select 5 union all
select 5 union all
select 2 union all
select 3 union all
select 1 union all
select 4.5 union all
select 5.1 union all
select 7.0 union all
select 8.0 union all
select 9.0 union all
select 'TBD'

select *
from @a
where isnumeric(columna) = 1
and columna between 1.0 and 5.0

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

hi,

Put single quotes while inserting numbers into @a table otherwise it will throw error

i dont think it will throw error. it will implicitly convert values to varchar

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 11:47:12

quote:
Originally posted by visakh16

quote:
Originally posted by raky

quote:
Originally posted by jhocutt

declare @a table (
columna Nvarchar(10)
)

insert into @a
select 5 union all
select 5 union all
select 2 union all
select 3 union all
select 1 union all
select 4.5 union all
select 5.1 union all
select 7.0 union all
select 8.0 union all
select 9.0 union all
select 'TBD'

select *
from @a
where isnumeric(columna) = 1
and columna between 1.0 and 5.0

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

hi,

Put single quotes while inserting numbers into @a table otherwise it will throw error

i dont think it will throw error. it will implicitly convert values to varchar

Hi Visakh,

Error converting data type varchar to numeric.

I got this error when i run the above query without keeping singlequotes...while inserting numbers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 11:54:20

what about this?


insert into @a 
select 'TBD' union all
select 5 union all
select 5 union all
select 2 union all
select 3 union all
select 1 union all
select 4.5 union all
select 5.1 union all
select 7.0 union all
select 8.0 union all
select 9.0

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 11:58:38

quote:

Originally posted by visakh16

what about this?


insert into @a 
select 'TBD' union all
select 5 union all
select 5 union all
select 2 union all
select 3 union all
select 1 union all
select 4.5 union all
select 5.1 union all
select 7.0 union all
select 8.0 union all
select 9.0

yaa this one also returning same error message

Msg 8114, Level 16, State 5, Line 5
Error converting data type varchar to numeric.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 12:05:10

oh ok... i think thats because of union all. try just insert a single value

insert into @a
select 5

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 12:10:02

quote:
Originally posted by visakh16

oh ok... i think thats because of union all. try just insert a single value

insert into @a
select 5

yes ur correct. when i executed the below query

declare @a table (
columna Nvarchar(10)
)

insert into @a
select 5

select * from @a

It is taking nearly 1 sec to retrieve the record

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 12:13:41

even the below will work. it might take more time to do implicit conversion

insert into @a
select 5 union all
select 5 union all
select 2 union all
select 3 union all
select 1 union all
select 4.5 union all
select 5.1 union all
select 7.0 union all
select 8.0 union all
select 9.0

dbechberger
Starting Member

2 Posts

Posted - 2009-01-09 : 17:27:36

The column data given above is only an example of the data. It is not feasible to UNION ALL possible combinations because it is almost unlimited. I tried doing a subquery in the from clause. The subquery returns all the correct numeric value but when used as the subquery I still get an error on convert. Could this be an order of operations sort of problem?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-10 : 02:19:04

Jsut want to point out that isnumeric() is not reliable

select isnumeric('12d3')

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:35:10

quote:
Originally posted by madhivanan

Jsut want to point out that isnumeric() is not reliable

select isnumeric('12d3')

Madhivanan

Failing to plan is Planning to fail

And you can use this instead

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources