SQL FUNCTION TO VALIDATE NUMERIC VALUES

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Vivek Nigam (ADN TECHNOLOGIES LIMITED)

-- Created date: 10-May-2016 @ 11:12 AM

-- Description: Sql Function  to validate numeric values.

-- ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.

-- ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). 

-- Modified By  :  Vivek Nigam (ADN TECHNOLOGIES LIMITED)

-- Modify On -        

-- Input - Pass The Value Which You want to Validate 

-- OutPut - 

-- Return - 1 = Valid

---Return - 0 - Invalid    

-- SQL Script File Name - scr_02_Function_To_Validate_Integer_Data_In_Staging.sql

-- This Function will be created / executed by Batch File Called -

 01_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat

  -- =============================================

--Check if a function exists on a ADN-TECHNOLOGIES-LIMITED-STAGING Database Before Creating - If exists Then Drop 

  IF object_id('dbo.fn_IsNumeric') IS NOT NULL

BEGIN

DROP FUNCTION [dbo].[fn_IsNumeric]

END

GO

  CREATE FUNCTION dbo.fn_IsNumeric

(

@Number VARCHAR(100)

)

RETURNS bit

BEGIN

DECLARE @Result bit 

IF @Number IS NULL   RETURN  0  --- IF NULL  THEN RETURN VALID INTEGER - ASK BY PAUL - DATED ON 08-09-2016 @ 11:12

IF LEFT(@Number, 1) = '-'

SET @Number = SUBSTRING(@Number, 2, LEN(@Number))

IF LEFT(@Number, 1) = '_'

SET @Number = SUBSTRING(@Number, 2, LEN(@Number))

IF (

    PATINDEX('%[^0-9-]%', @Number) = 0

    AND CHARINDEX('-', @Number) <= 1 

AND @Number NOT IN ('.', '-', '+', '^')  

AND LEN(@Number)>0

AND @Number NOT LIKE '%-%'

)

SET @Result = 1

ELSE

SET @Result = 0

RETURN IIF(@Result = 1,0,1)

END

GO