Casting char to int but not causing error

Hi. In the where clause of my query I will be converting a char column to an
int to match it to another int column. But the char could contain characters
and not convert to an int. In that case I don't want the query to fail but
simply not do the match. Is that possible?
select * from A, B where cast ( A.CharCol as int ) = B.IntCol
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]Hi
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('441')
INSERT INTO #Test VALUES ('55a')
SELECT CAST(col AS INT) FROM #Test
--Server: Msg 245, Level 16, State 1, Line 1
--Syntax error converting the varchar value '55a' to a column of data type
int.
SELECT CAST(col AS INT) FROM #Test WHERE ISNUMERIC(col)=1
You can visit at Aaron's web site www.aspfaq.com to find a script as
alternative to ISNUMERIC() function
"McGy" <anon@.anon.com> wrote in message
news:eiVV8j1wFHA.2620@.TK2MSFTNGP09.phx.gbl...
> Hi. In the where clause of my query I will be converting a char column to
> an
> int to match it to another int column. But the char could contain
> characters
> and not convert to an int. In that case I don't want the query to fail but
> simply not do the match. Is that possible?
> select * from A, B where cast ( A.CharCol as int ) = B.IntCol
> Thanks.
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>|||Thanks Uri. Unfortunately your example does not work for me. I am not
selecting the char column as an int but rather using it in the where clause
as an int.
Thankfully I have just figured it out myself using the AND clause as
follows - try with A set to 'a' then A set to '1':
declare @.A as char (1)
set @.A = 'a'
select
1
where
( isnumeric ( @.A ) = 1 )
and
( cast ( @.A as int ) = 1 )
McGy
[url]http://mcgy.blogspot.com[/url]
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$4xKx1wFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi
> CREATE TABLE #Test
> (
> col VARCHAR(10)
> )
> INSERT INTO #Test VALUES ('441')
> INSERT INTO #Test VALUES ('55a')
> SELECT CAST(col AS INT) FROM #Test
> --Server: Msg 245, Level 16, State 1, Line 1
> --Syntax error converting the varchar value '55a' to a column of data
type
> int.
> SELECT CAST(col AS INT) FROM #Test WHERE ISNUMERIC(col)=1
>
> You can visit at Aaron's web site www.aspfaq.com to find a script as
> alternative to ISNUMERIC() function
>
> "McGy" <anon@.anon.com> wrote in message
> news:eiVV8j1wFHA.2620@.TK2MSFTNGP09.phx.gbl...
to
but
>|||Hi
Actually ,I only tried to give you an idea for solving the problem.
"McGy" <anon@.anon.com> wrote in message
news:eDZ8cA2wFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks Uri. Unfortunately your example does not work for me. I am not
> selecting the char column as an int but rather using it in the where
> clause
> as an int.
> Thankfully I have just figured it out myself using the AND clause as
> follows - try with A set to 'a' then A set to '1':
> declare @.A as char (1)
> set @.A = 'a'
> select
> 1
> where
> ( isnumeric ( @.A ) = 1 )
> and
> ( cast ( @.A as int ) = 1 )
>
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$4xKx1wFHA.3720@.TK2MSFTNGP14.phx.gbl...
> type
> to
> but
>|||Hi Uri,
isNumeric() doesn't work in all cases. For example:
select isnumeric('34e5') -- return 1
select cast('34e5' as int) -- return error
Back to your problem, try this:
select * from A, B
where case when A.CharCol not like '%^[0-9]%' then null else cast (
A.CharCol as int ) end = B.IntCol|||Thanks for that clarification!
McGy
[url]http://mcgy.blogspot.com[/url]
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1127833853.921282.306230@.f14g2000cwb.googlegroups.com...
> Hi Uri,
> isNumeric() doesn't work in all cases. For example:
> select isnumeric('34e5') -- return 1
> select cast('34e5' as int) -- return error
> Back to your problem, try this:
> select * from A, B
> where case when A.CharCol not like '%^[0-9]%' then null else cast (
> A.CharCol as int ) end = B.IntCol
>|||Tam
I you read my post carefully , you would see what I wrote at the ned of the
post
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1127833853.921282.306230@.f14g2000cwb.googlegroups.com...
> Hi Uri,
> isNumeric() doesn't work in all cases. For example:
> select isnumeric('34e5') -- return 1
> select cast('34e5' as int) -- return error
> Back to your problem, try this:
> select * from A, B
> where case when A.CharCol not like '%^[0-9]%' then null else cast (
> A.CharCol as int ) end = B.IntCol
>|||Uri,
indeed I didn't read your post carefully. My post was meant to the
original poster ( = McGy), but I thoguth it was you ;)
cheers,