Can I determine if a value is numberic then output 'text' else output
'some other text'?
I'm trying to do this in a select statement.
Thanks!
'some other text'?
I'm trying to do this in a select statement.
Thanks!
-
-
Re: if-then in select stmnt
anderma8 wrote:[color=blue]
> Can I determine if a value is numberic then output 'text' else output
> 'some other text'?
>
> I'm trying to do this in a select statement.
> Thanks!
>[/color]
SELECT CASE WHEN ISNUMERIC(some_ column)
THEN 'text'
ELSE 'some other text'
END AS theNumericTest
FROM table_name
.... etc. ...
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-
Re: if-then in select stmnt
MGFoster (me@privacy.com ) writes:[color=blue]
> SELECT CASE WHEN ISNUMERIC(some_ column)
> THEN 'text'
> ELSE 'some other text'
> END AS theNumericTest
> FROM table_name
> ... etc. ...[/color]
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_colum n NOT LIKE '%^[0-9]%'"
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at
Comment
-
Re: if-then in select stmnt
Erland Sommarskog wrote:[color=blue]
> MGFoster (me@privacy.com ) writes:
>[color=green]
>>SELECT CASE WHEN ISNUMERIC(some_ column)
>> THEN 'text'
>> ELSE 'some other text'
>> END AS theNumericTest
>>FROM table_name
>>... etc. ...[/color]
>
>
> 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_colum n NOT LIKE '%^[0-9]%'"
>
>
>[/color]
-----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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRIxuWoechKq OuFEgEQLaIQCg/ndIiAGgGGMqbXsO tIrFB9KCWjsAn3k V
3m0xI3cbdPwhahJ Tlhod1p7S
=2I7M
-----END PGP SIGNATURE-----
Comment
-
Re: if-then in select stmnt
On Sun, 11 Jun 2006 19:27:24 GMT, MGFoster wrote:
[color=blue]
>You're correct about the Isnumeric(...) requiring the "=1." You're
>incorrect about the NOT LIKE expression.[/color]
Hi MGFoster,
That's right. Erland misplaced the ^ character, He should have typed
NOT LIKE '%[^0-9]%'
[color=blue]
>set nocount on
>create table #t (c char(6))[/color]
You should change this to varchar. Or, if you want to keep this as char,
add a call to RTRIM() in the code. Fixed length character strings get
padded with space characters which are, clearly, not numeric.
(snip)[color=blue]
>select c,
> case when isnumeric(c)=1
> then 'T'
> else 'F'
> end as IsNumericTest,
>
> case when c NOT LIKE '%^[0-9]%'[/color]
Correct the line above to
case when c NOT LIKE '%[^0-9]%'[color=blue]
> then 'T'
> else 'F'
> end as NotLikeTest
>
>from #t
>
>drop table #t
>
>Results:[/color]
c IsNumericTest NotLikeTest
------ ------------- -----------
ab12 F F
1112 T F
cd12 F F
3312 T F
(*)^ F F
$25.10 T F
$25^2 F F
--
Hugo Kornelis, SQL Server MVP
Comment
-
Re: if-then in select stmnt
MGFoster (me@privacy.com ) writes:[color=blue]
> Erland Sommarskog wrote:[color=green]
>> 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_colum n NOT LIKE '%^[0-9]%'"[/color]
>
> 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.:[/color]
Sorry, that was a typo, and bad proof-reading. The pattern should
of course be '%[^0-9]%'" as Hugo was kind to fill in for me.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at
Comment
Comment