Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Enhanced ISNUMERIC() function

Aug 27 2007 9:30AM by Madhivanan   

It seems often users want to check whether the data has only numbers in a varchar type column. The commonly suggested one is to make use of ISNUMERIC() function. But the problem in using that function is that it will treat some alphabets, $, char(10),etc as numbers.

Consider this example

declare @test table(strings varchar(50))

Insert into @test
Select '12d3' union all
Select '87234.45' union all
Select '$123,456.00' union all
Select ' 12 ' union all
Select char(10) union all
Select '$'
select strings,isnumeric(strings)as valid from @test 

The result is

strings                                             valid       
-----------------------------------------------    -------- 

12d3                                                  1
87234.45                                              1
$123,456.00                                           1
12                                                    1
                                                    1
$                                                     1

Note that only 87234.45 should be considered as numeric and not others The following function will solve this problem

CREATE Function Is_numeric(@value varchar(25))
Returns bit
as
Begin
Return
    (
    case when @value not like '%[^-0-9.]%' and len(@value)-len(replace(@value,'.',''))<2
        and 
        1=
        (
            case when charindex('-',@value)>0 then
            case when left(@value,1)='-' and len(@value)-len(replace(@value,'-',''))<2 and len(@value)>1 then 
                1 
            else 
                0 
            end
        else
                1
        end
    ) then
        1
    else
        0
    end
    )
End

Now execute this select statement

select strings, dbo.is_numeric(strings)as valid from @test

The result is

strings                                              valid 
-----------------------------------------------      ----- 
12d3                                                   0
87234.45                                               1
$123,456.00                                            0
12                                                     0
                                                     0
$                                                      0

Tags: t-sql, sql_server, isnumeric,


Madhivanan
3 · 40% · 12862
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Its worked for me

    Thanks for sharing.

    commented on Jan 17 2013 3:24AM
    Bala Krishna
    83 · 2% · 678
  • Thanks Balakrishna fro the feedback

    commented on Jan 17 2013 4:27AM
    Madhivanan
    3 · 40% · 12862

Your Comment


Sign Up or Login to post a comment.

"Enhanced ISNUMERIC() function" rated 5 out of 5 by 4 readers
Enhanced ISNUMERIC() function , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]