convert INT

I am writing a sql statement similiar to:
select * from testtable where id in (select id from anothertable).
The ID field in anothertable is int whereas the ID field in testtable is
varchar(originally created by someone else).
99% of the data in the ID field of testtable is an INT, so I usually do
not have a problem. But a couple of the entries are text.
How can I ignore anything in the ID field of testtable that is not and
INT? I currently get an error: Syntax error converting the varchar value
'abcabc' to a column of data type int.
Thank you
*** Sent via Developersdex http://www.examnotes.net ***select "name the columns-don't use *" from testtable T where exists (select
id from anothertable where id = Cast(T.ID as int))
where isnumeric(T.id) = 1
There are some issues with Isnumeric.
Come back to us if you have a problem.
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:eANO76h5FHA.724@.TK2MSFTNGP14.phx.gbl...
>I am writing a sql statement similiar to:
> select * from testtable where id in (select id from anothertable).
> The ID field in anothertable is int whereas the ID field in testtable is
> varchar(originally created by someone else).
> 99% of the data in the ID field of testtable is an INT, so I usually do
> not have a problem. But a couple of the entries are text.
> How can I ignore anything in the ID field of testtable that is not and
> INT? I currently get an error: Syntax error converting the varchar value
> 'abcabc' to a column of data type int.
>
> Thank you
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You can use the ISNUMERIC( ) function in your WHERE clause to test the
column value. This will filter it from the second table to avoid any join
errors.
WHERE ISNUMERIC(TableWithVarChars.KeyField)
HTH,
John Scragg
"Joey Martin" wrote:

> I am writing a sql statement similiar to:
> select * from testtable where id in (select id from anothertable).
> The ID field in anothertable is int whereas the ID field in testtable is
> varchar(originally created by someone else).
> 99% of the data in the ID field of testtable is an INT, so I usually do
> not have a problem. But a couple of the entries are text.
> How can I ignore anything in the ID field of testtable that is not and
> INT? I currently get an error: Syntax error converting the varchar value
> 'abcabc' to a column of data type int.
>
> Thank you
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Joey,
ISNUMERIC is an interesting, but not useful function in this case because
"ISNUMERIC returns 1 when the input expression evaluates to a valid integer,
floating point number, money or decimal type; otherwise it returns 0."
Characters such as $ , E are still allowed.
In any case, whether by ISNUMERIC or by NOT LIKE [list of characters you
don't want] you could try to filter the rows. However the optimizer can
choose to filter rows first, as you would want, or filter them last, after
it is too late to you, and that is not under your control.
Best bet: Get the data in anothertable cleaned up and then make the ID an
INT.
Assuming you cannot do that: CAST your INT as VARCHAR so that it matches
anothertable.
RLF
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:eANO76h5FHA.724@.TK2MSFTNGP14.phx.gbl...
>I am writing a sql statement similiar to:
> select * from testtable where id in (select id from anothertable).
> The ID field in anothertable is int whereas the ID field in testtable is
> varchar(originally created by someone else).
> 99% of the data in the ID field of testtable is an INT, so I usually do
> not have a problem. But a couple of the entries are text.
> How can I ignore anything in the ID field of testtable that is not and
> INT? I currently get an error: Syntax error converting the varchar value
> 'abcabc' to a column of data type int.
>
> Thank you
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Here a function for that check that can replace Isnumeric.
This checks for all valid numbers including Decimal.
It could easily be adapted for INTs only.
CREATE FUNCTION dbo.IsReallyNumeric
(@.num VARCHAR(19))
RETURNS BIT
BEGIN
RETURN CASE
WHEN LEFT(@.num,1) LIKE '[-0-9+.]'
AND PATINDEX('%[^0-9.]%', SUBSTRING(@.num, 2, 18)) = 0
AND LEN(@.num) - LEN(REPLACE(@.num, '.', '')) <=1
THEN 1
ELSE 0
END
END
I'd give credit to the person that wrote this if I remembered who it was.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ushNbPi5FHA.1032@.TK2MSFTNGP11.phx.gbl...
> Joey,
> ISNUMERIC is an interesting, but not useful function in this case because
> "ISNUMERIC returns 1 when the input expression evaluates to a valid
> integer, floating point number, money or decimal type; otherwise it
> returns 0." Characters such as $ , E are still allowed.
> In any case, whether by ISNUMERIC or by NOT LIKE [list of characters you
> don't want] you could try to filter the rows. However the optimizer can
> choose to filter rows first, as you would want, or filter them last, after
> it is too late to you, and that is not under your control.
> Best bet: Get the data in anothertable cleaned up and then make the ID an
> INT.
> Assuming you cannot do that: CAST your INT as VARCHAR so that it matches
> anothertable.
> RLF
> "Joey Martin" <joey@.infosmiths.net> wrote in message
> news:eANO76h5FHA.724@.TK2MSFTNGP14.phx.gbl...
>|||Thanks for the help, but I am receiving another error now:
Here is the actual query:
select
la_code,mls_acct,street_name,street_num,
street_dir,city,current_price,'r
es' as prop_type,cast(substring(REMARKS, 1, 5999) as varchar(6000)) AS
[REMARKS],udf_photo0
from glar_mlsdata_res t where
exists (select
agentid from glar_clients where agentid = Cast(T.la_code as int))
where isnumeric(T.la_code) = 1
ERROR: Incorrect syntax near the keyword 'where'.
*** Sent via Developersdex http://www.examnotes.net ***|||Sorry, I made a mistake on my answer.
Change:
where isnumeric(T.la_code) = 1
to
and isnumeric(T.la_code) = 1
"Joey Martin" <joey@.infosmiths.net> wrote in message
news:uq0KyWi5FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Thanks for the help, but I am receiving another error now:
> Here is the actual query:
> select
> la_code,mls_acct,street_name,street_num,
street_dir,city,current_price,'r
> es' as prop_type,cast(substring(REMARKS, 1, 5999) as varchar(6000)) AS
> [REMARKS],udf_photo0
> from glar_mlsdata_res t where
> exists (select
> agentid from glar_clients where agentid = Cast(T.la_code as int))
> where isnumeric(T.la_code) = 1
>
> ERROR: Incorrect syntax near the keyword 'where'.
> *** Sent via Developersdex http://www.examnotes.net ***|||Interesting, but I don't think that it resolves the optimizer precedence
issue to a query. The optimizer has lots of choices, but what we care about
here is these two choices:
1 - Run IsReallyNumeric and filter on the bit
2 - Cast the Numeric as INT and join the tables
Although ideally they would run in 1, 2 order, the optimizer is equally
happy choosing 2, 1 order. But 2, 1 still causes errors. (I have seen code
depending on 1, 2 order run for months or years then, one day, switch to
2, 1 and cause failures.)
Something that will work is to create a Multi-Statement Table-Valued User
Defined Function that uses the IsReallyNumeric to prefilter the rows. That
step _is_ forced to run first, before choosing other optimizer strategies.
(But an In-Line Table-Valued UDF will not force precedence.)
RLF
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23JCTcUi5FHA.2552@.TK2MSFTNGP10.phx.gbl...
> Here a function for that check that can replace Isnumeric.
> This checks for all valid numbers including Decimal.
> It could easily be adapted for INTs only.
> CREATE FUNCTION dbo.IsReallyNumeric
> (@.num VARCHAR(19))
> RETURNS BIT
> BEGIN
> RETURN CASE
> WHEN LEFT(@.num,1) LIKE '[-0-9+.]'
> AND PATINDEX('%[^0-9.]%', SUBSTRING(@.num, 2, 18)) = 0
> AND LEN(@.num) - LEN(REPLACE(@.num, '.', '')) <=1
> THEN 1
> ELSE 0
> END
> END
> I'd give credit to the person that wrote this if I remembered who it was.
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:ushNbPi5FHA.1032@.TK2MSFTNGP11.phx.gbl...
>|||On Thu, 10 Nov 2005 16:30:31 -0500, Russell Fields wrote:

>Interesting, but I don't think that it resolves the optimizer precedence
>issue to a query. The optimizer has lots of choices, but what we care abou
t
>here is these two choices:
>1 - Run IsReallyNumeric and filter on the bit
>2 - Cast the Numeric as INT and join the tables
>Although ideally they would run in 1, 2 order, the optimizer is equally
>happy choosing 2, 1 order. But 2, 1 still causes errors. (I have seen cod
e
>depending on 1, 2 order run for months or years then, one day, switch t
o
>2, 1 and cause failures.)
>Something that will work is to create a Multi-Statement Table-Valued User
>Defined Function that uses the IsReallyNumeric to prefilter the rows. That
>step _is_ forced to run first, before choosing other optimizer strategies.
>(But an In-Line Table-Valued UDF will not force precedence.)
Hi Russell,
Another way to force the evaluation order is to use a CASE expression.
For a join, you'd get something like
FROM Tab1
INNER JOIN Tab2
ON Tab2.ReallyNumeric = CASE WHEN IsNumeric(Tab1.BadColumn)
THEN CAST(Tab1.BadColumn AS int)
END
If the bad column is not numeric (and IsReallyNumeric or an inline CASE
expression could be used instead of IsNumeric), the CASE will evaluate
to NULL, the join condition evaluates to Unknown and the row is not
included.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)