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...
Add a comment
|
2 Answers
IsNumeric() function returns 1 for strings (varchars) which can be converted to a number and 0 for those that cannot..
Check out IsNumeric function
Comments
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
