Clever Geek Handbook

Validate numeric value with SQL Server 2000

How do I know if a VARCHAR field value can be successfully converted to an integer?

I want to do it in bulk to insert records from one table to another ...

+2


source to share


2 answers


IsNumeric () returns 1 for strings (varchars) that can be converted to a number and 0 for those that cannot.



Check out the IsNumeric Function

+8


source


One problem with the IsNumeric () function is that you get True and if the number got a decimal separator, which is perfectly correct, but if anyone needs to check directly for numbers in varchar, no decimal characters (I got this when I it was necessary to calculate the CHECK-digit on the barcode). You can use castom made function like



create FUNCTION [dbo].[checkbarkod] 
(
    @ean_kod varchar(13)
)
RETURNS bit
AS
begin
    declare @duzina int
    declare @slovo char(1)
    declare @pozicija int
    declare @uredu bit
    set @duzina=len(@ean_kod) 
    while @duzina>0
        begin
            set @slovo=(substring(@ean_kod,@duzina,1))
            if  (@slovo not in('1','2','3','4','5','6','7','8','9','0'))
                begin
                    set @uredu=convert(bit,0)
                    break
                end
            else 
                begin
                    set @uredu=convert(bit,1)
                    set @duzina=@duzina-1
            end
        end
    RETURN @uredu
end
0


source






More articles:


All Articles