Posted by Hugo Kornelis on 06/11/06 20:27

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

• Next in forum: Re: A basic question: Removing duplicate results from Max function
• Prev in forum: Re: Backup log - will it truncate?
• Thread view: Re: if-then in select stmnt

[Reply to this message]