CODEWITHSUNDEEP
sql-serversql-server-2000numeric
tekBlues
tekBlues

Reputation: 5793

Check for Numeric Value using SQL Server 2000

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

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

Upvotes: 2

Views: 3061

Answers (2)

adopilot
adopilot

Reputation: 4500

One issue whit IsNumeric() function is that You will get True and if number got decimal separator, What is totally right, But if someone as I need to check straight to numbers in varchar, without decimal symbols, (I got that when I needed to calculate CHECK digit on 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

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146419

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

Check out IsNumeric function

Upvotes: 8

Related Questions