Posted by MGFoster on 06/11/06 19:27

Erland Sommarskog wrote:
> MGFoster (me@privacy.com) writes:
>
>>SELECT CASE WHEN ISNUMERIC(some_column)
>> THEN 'text'
>> ELSE 'some other text'
>> END AS theNumericTest
>>FROM table_name
>>... etc. ...
>
>
> 1) CASE WHEN isnumeric(some_column) = 1
>
> 2) isnumeric is virtually useless because it approves anything that
> can be converted a to numeric data type. For test on "all digits",
> this is better: "some_column NOT LIKE '%^[0-9]%'"
>
>
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're correct about the Isnumeric(...) requiring the "=1." You're
incorrect about the NOT LIKE expression. The NOT LIKE expression will
return True for all columns that have both alpha chars and numeric
chars. E.g.:

set nocount on
create table #t (c char(6))
go

insert into #t (c) values ('ab12')
insert into #t (c) values ('1112')
insert into #t (c) values ('cd12')
insert into #t (c) values ('3312')
insert into #t (c) values ('(*)^')
insert into #t (c) values ('$25.10')
insert into #t (c) values ('$25^2')

go

select c,
case when isnumeric(c)=1
then 'T'
else 'F'
end as IsNumericTest,

case when c NOT LIKE '%^[0-9]%'
then 'T'
else 'F'
end as NotLikeTest

from #t

drop table #t

Results:
c IsNumericTest NotLikeTest
------ ------------- -----------
ab12 F T
1112 T T
cd12 F T
3312 T T
(*)^ F T
$25.10 T T
$25^2 F F

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIxuWoechKqOuFEgEQLaIQCg/ndIiAGgGGMqbXsOtIrFB9KCWjsAn3kV
3m0xI3cbdPwhahJTlhod1p7S
=2I7M
-----END PGP SIGNATURE-----

[Back to original message]