|
|
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-----
Navigation:
[Reply to this message]
|