By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

if-then in select stmnt

P: n/a
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!

Jun 9 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
anderma8 wrote:
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!


SELECT CASE WHEN ISNUMERIC(some_column)
THEN 'text'
ELSE 'some other text'
END AS theNumericTest
FROM table_name
.... etc. ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Jun 9 '06 #2

P: n/a
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]%'"

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 9 '06 #3

P: n/a
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-----
Jun 11 '06 #4

P: n/a
On Sun, 11 Jun 2006 19:27:24 GMT, MGFoster wrote:
You're correct about the Isnumeric(...) requiring the "=1." You're
incorrect about the NOT LIKE expression.
Hi MGFoster,

That's right. Erland misplaced the ^ character, He should have typed
NOT LIKE '%[^0-9]%'
set nocount on
create table #t (c char(6))
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)select c,
case when isnumeric(c)=1
then 'T'
else 'F'
end as IsNumericTest,

case when c NOT LIKE '%^[0-9]%' Correct the line above to
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 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
Jun 11 '06 #5

P: n/a
MGFoster (me@privacy.com) writes:
Erland Sommarskog wrote:
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]%'"


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.:


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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 11 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.