Sql 7 cast - 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

Sql 7 cast

Author

Topic

tarz
Starting Member

31 Posts

Posted - 2009-03-11 : 15:07:05

I am trying to capture two values substruct on the fly and display only when result is 0

ie. (how to make it work)
SELECT a, b, (cast(num1 as int) - cast(num2 as int)) as "result"
FROM testTable
where (cast(num1 as int) - cast(num2 as int))=0

ps: might need to use a float cause the num1, and num2 is 12 digit numer (111009999999)

Thank you.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-11 : 15:12:51

Whats wrong with the one you tried?

tarz
Starting Member

31 Posts

Posted - 2009-03-11 : 15:21:55

Hi, I get an error:

//-->If I use (int):
where (cast(num1 as int) - cast(num2 as int))=0

Error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'OR152461' to a column of data type int.

//-->If I use (float):
where (cast(num1 as float) - cast(num2 as float))=0

Error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

The WHERE clause fails..

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-11 : 15:29:19

You are trying to convert a varchar value 'OR152461' to int ot float.

I'm assuming your column has both numeric as well as alphanumeric vlaues.
What is the logic for subtraction? DO you want to skip the alphanumeric data and work on numeric data alone? Can you give some sample data and explain how the subtraction should happen.

tarz
Starting Member

31 Posts

Posted - 2009-03-11 : 15:36:47

Thanks for your help,
The columns I am substructing are only numbers:
(ie)
A B result
------------- ------------- -----------
000001999999 000001000000 999999
0000040678679 0000040678670 9
0000041999999 0000041000000 999999
000005020999 000005020000 999
000005031999 000005031000 999
0000054133009 0000054133000 9
0000054133029 0000054133020 9
0000054133039 0000054133030 9
0000075025399 0000075025390 9

I have over 5,000 records and I want to show only the result = 0.

I hope it is more clear now what I am trying to do.

Thank you.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-11 : 15:48:23

I tried with your sample data and it worked fine..

declare @t table (a varchar(250), b varchar(250))
insert @t
select '000001999999','000001000000' union all
select '0000041999999','0000041000000' union all
select '0000054133009','0000054133009' union all
select '0000075025399','0000075025390' union all
select '0000075025399','0000075025399'

select * from @t where (cast(a as int) - cast(b as int)) = 0

I think you have some non-numeric data sitting there.

Can you run this and see if it returns something?

select * from yourtable where isnumeric(a) = 0 or isnumeric(b) = 0

tarz
Starting Member

31 Posts

Posted - 2009-03-11 : 16:05:42

You absolutely correct, there is alphanumeric values, I didnt even know, sorry about that. I guess I will have to include is not isnumeric(a) and is not isnumeric(b) in the select query.. :)

Thank you

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-11 : 16:51:42

Great that you figured it out.

Check this thread. Might help.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121443

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources