SQL Function to Remove All Spaces From String

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Created date: 15-January-2020 @ 09:24 AM

-- Description: Sql Function  to Remove_All_Spaces From String

--- File Name - Fn_07_Function_To_Remove_All_Spaces.sql

--- fn_RemoveAllSpaces

-- Modified By  :  Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Modify On -               

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

--Check if a function exists in Database Before Creating - If exists Then Drop 

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

BEGIN

DROP FUNCTION [dbo].fn_RemoveAllSpaces

END

GO

CREATE FUNCTION fn_RemoveAllSpaces

(

@InputStr VARCHAR(100)

)

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @ResultStr varchar(8000)

SET @ResultStr = @InputStr

WHILE CHARINDEX(' ', @ResultStr) > 0

SET @ResultStr = REPLACE(@InputStr, ' ', '')

RETURN @ResultStr

END

SQL Function to Check UK National Insurance Number

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]

--  Created date: 14-January-2020 @ 09:33 AM

--  Description: Sql Function  to Check UK National Insurance Number 

--- Check UK NationalInsurance If Not Correct Retrun  0

--- File Name - Fn_06_Function_To_Validate_UK_NationalInsuranceNumber_NI.sql

--- fn_ValidateUKNationalInsuranceNumber

--  Modified By  :  Vivek Nigam [ADN TECHNOLOGIES LIMITED]

--  Modify On -               

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

--Check if a function exists in Database Before Creating - If exists Then Drop 

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

BEGIN

DROP FUNCTION [dbo].[fn_ValidateUKNationalInsuranceNumber]

END

GO

CREATE FUNCTION [dbo].[fn_ValidateUKNationalInsuranceNumber] (@NINo VARCHAR(20))

  RETURNS bit

  AS 

  BEGIN

    DECLARE @Result bit

IF UPPER(@NINo) LIKE 

  '[A-CEGHJ-PR-TW-Za-ceghj-pr-tw-z][A-CEGHJ-NPR-TW-Za-ceghj-npr-tw-z][0-9][0-9][0-9][0-9][0-9][0-9]'

      --'[A-CEGHJ-PR-TW-Z][A-CEGHJ-NPR-TW-Z]

      -- [0-9][0-9][0-9][0-9][0-9][0-9][A-D ]'

  OR UPPER(@NINo) LIKE 

  '[A-CEGHJ-PR-TW-Za-ceghj-pr-tw-z][A-CEGHJ-NPR-TW-Za-ceghj-npr-tw-z][0-9][0-9][0-9][0-9][0-9][0-9][A-DFMa-dfm]'

    --AND UPPER(left(@NINo,2)) NOT IN 

    --   ('BG','GB','KN','NK','NT','TN','ZZ')

      -- Valid

      SET @Result = 1

    ELSE

      -- Invalid

      SET @Result = 0

    RETURN @Result

  END

SQL FUNCTION TO VALIDATE POST CODE USING GRIDALL FILE

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Created date: 04-August-2016 @ 14:51 PM

-- Description: Sql Function  to validate POST CODE USING Gridall file.

----latest Gridall file have been downloaded , there are total 25,829,71  post code records. 

--Data has been migrated in staging table. Scribe DTS has been  created to import the  file in staging table (Table name - scr_z_UK_PostCode_Validation_Table_Using_Gridall).

-- 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 POST CODE Value Which You want to Validate 

-- OutPut - 

-- Return - 0 = Valid

---Return - 1 - Invalid    

-- SQL Script File Name - scr_24_Function_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging.sql

-- This Function will be created / excecated by Batch File Called - 03_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat

-- Table name - scr_z_UK_PostCode_Validation_Table_Using_Gridall

-- To Test - Select dbo.fn_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging('KT13 0RH') FROM  scr_z_UK_PostCode_Validation_Table_Using_Gridall AS PO

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

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

  IF object_id('dbo.fn_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging', N'FN') IS NOT NULL

BEGIN

DROP FUNCTION [dbo].fn_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging

END

GO

  CREATE FUNCTION dbo.fn_To_Validate_PostCode_Using_Gridall_Data_File_In_Staging

(

@vPostCode VARCHAR(100)

)

RETURNS INT

AS

-- Returns the 0 If Post Code Is Valid .  1 - If Post Code Is InValid.

BEGIN  

DECLARE @vRET INT;  

IF @vPostCode = '' RETURN 0 -- Check If @PostCode Have  Null String Then Return As Valid Post Code  - Check If @PostCode Have Null String 

IF @vPostCode IS  NULL RETURN 0 -- Check If @PostCode Have  Null String Then Return As Valid Post Code - Check If @PostCode Have Null Value 

IF LEN(@vPostCode) < 3 RETURN 1 -- Add to Fix The Issue If The Post Code Is Only 2 Char. - Is INVALID

SET @vPostCode=UPPER(Replace(@vPostCode,' ',''))

SET @vPostCode= LEFT(@vPostCode,LEN(@vPostCode)-3)+' '+ Right(@vPostCode,3)

SELECT @vRET = COUNT(*)   

FROM scr_z_UK_PostCode_Validation_Table_Using_Gridall PO  

WHERE PO.PostCode_2 = @vPostCode; 

--WHERE PO.PostCode_2 = @vPostCode; 

RETURN IIF(@vRET >= 1,0,1);  

END;  

GO

SQL FUNCTION TO VALIDATE UK POST CODE

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Created date: 20-May-2016 @ 09:23 AM

-- Description: Sql Function  to validate UK POST Code.

-- ISDATEFORMATCorrect returns 1 when the input expression evaluates to a valid PostCode ; otherwise it returns 0.

-- Modified By  :  Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Modify On -        

-- Input - Pass The PostCode You want to Validate 

-- OutPut - 

-- Return - 1 = Valid

---Return - 0 - Invalid    

-- SQL Script File Name - scr_05_Function_To_Validate_Date_Format_In_Staging.sql

-- This Function will be Find Non-Ascii Characters in One Varchar Column or Mulitiple Columns using SQL Server

-- This Function Will Creatd / excuated by Batch File Called - 01_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat

-- Here is a UDF I built to detectc columns with extended ascii charaters. It is quick and you can 

-- extended the character set you want to check. The second parameter allows you to switch between checking anything outside the standard character set or allowing an extended set:

--USAGE:

--select Address1 

--from PropertyFile_English

--where fn_ContainsNonPrintableChars(Address1, 1) = 1

--File Name - scr_05_Function_To_Validate_UK_PostCode_Format_In_Staging.sql

--Modify By 

--Modifyed Date - 

--How To Test / USe..

--DECLARE @TestTab Table (postcode varchar(50) not null)

--Insert @TestTab values('SK13 8LY') --Valid

--Insert @TestTab values('M1 1AA') --Valid

--Insert @TestTab values('M60 1NW') --Valid

--Insert @TestTab values('GIR 0AA') --Valid

--Insert @TestTab values('CR2 6XH') --Valid

--Insert @TestTab values('DN55 1PT') --Valid

--Insert @TestTab values('W1A 1HQ') --Valid

--Insert @TestTab values('EC1A 1BB') --Valid

--Insert @TestTab values('India') --Invalid

--Insert @TestTab values('12345') --Invalid

--Insert @TestTab values('Test SK13 8LY') --In-Valid

--INSERT @TestTab VALUES('XA1 1AA') --In-Valid

--INSERT @TestTab VALUES('AAA 1AA') --In-Valid

--INSERT @TestTab VALUES('RH16 1BQ') --Valid

--INSERT @TestTab VALUES('RH16 1BQZZZZZ')  --In-Valid

--INSERT @TestTab VALUES('RH16 1BQZZ')  --In-Valid

--INSERT @TestTab VALUES('ZZZ')  --In-Valid 

--INSERT @TestTab VALUES('CR02EN')  --Valid

--INSERT @TestTab VALUES('CR0 2EN')  --Valid

--INSERT @TestTab VALUES('CR0 2ENZZ')  --In-Valid

--SELECT  PostCode,CASE WHEN dbo.fn_ValidatePostCodeUK(PostCode) IS NULL THEN '0' 

--                    ELSE 1 END FROM   @TestTab

----As you can see, the query uses T-SQL's PATINDEX() function. This function searches a string for a 

----sub-string that matches a wildcard pattern. It returns the starting position of the pattern within the string, 

----or zero if the pattern is not found. In this case, we call PATINDEX() six times per row, 

----once for each of the possible postcode patterns.

-- A more refined regex, which excludes all invalid areas and some invalid districts is:

-- (GIR 0AA)|

--(((A[BL]|B[ABDFHLNRSTX]?|

--C[ABFHMORTVW]|D[ADEGHLNTY]|

--E[HNX]?|F[KY]|G[LUY]?|

--H[ADGPRSUX]|

--I[GMPV]|

--JE|

--K[ATWY]|

--L[ADELNSU]?|

--M[EKL]?|

--N[EGNPRW]?|

--O[LX]|

--P[AEHLOR]|

--R[GHM]|

--S[AEGKLMNOPRSTY]?|

--T[ADFNQRSW]|

--UB|

--W[ADFNRSV]|

--YO|

--ZE)[1-9]?[0-9]|((E|N|NW|SE|SW|W)1|EC[1-4]|WC[12])[A-HJKMNPR-Y]|(SW|W)([2-9]|[1-9][0-9])|EC[1-9][0-9]) [0-9][ABD-HJLNP-UW-Z]{2})

----/^(?:GIR 0AA|(?:(?:(?:A[BL]|B[ABDHLNRSTX]?|C[ABFHMORTVW]|D[ADEGHLNTY]|E[HNX]?|F[KY]|G[LUY]?|H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGK-PRSTY]?|T[ADFNQRSW]|UB|W[ADFNRSV]|YO|ZE)[1-9]?\d|(?:(?:E|N|NW|SE|SW|W)1|EC[1-4]|WC[12])[A-HJKMNPR-Y]|(?:SW|W)(?:[2-9]|[1-9]\d)|EC[1-9]\d)\d[ABD-HJLNP-UW-Z]{2}))$/i

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

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

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

BEGIN

DROP FUNCTION [dbo].fn_ValidatePostCodeUK

END

GO

 CREATE FUNCTION dbo.fn_ValidatePostCodeUK

(

@PostCode VARCHAR(100)

)

RETURNS VARCHAR(8)

AS

BEGIN             

       IF @PostCode = '' RETURN 'Valid' -- Asked By Paul to Check If @PostCode Have  Null String Then Return As Valid Post Code  - Check If @PostCode Have Null String 

   IF @PostCode IS  NULL RETURN 'Valid' -- Asked By Paul to Check If @PostCode Have  Null String Then Return As Valid Post Code - Check If @PostCode Have Null Value 

    IF LEN(@PostCode) < 3 RETURN NULL -- Add to Fix The Issue If The Post Code Is Only 2 Char. - Is INVALID

       

SET @PostCode=UPPER(Replace(@PostCode,' ',''))

SET @PostCode= LEFT(@PostCode,LEN(@PostCode)-3)+' '+ Right(@PostCode,3)

        RETURN        

              

COALESCE(

                           -- AANN NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 8),

                           -- AANA NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 8),

                           -- ANN NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 7),

                           -- AAN NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 7),

                           -- ANA NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 7),

                           --   AN NAA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @PostCode + ' '), 0), 6),

                           --   Special case GIR 0AA

                           SUBSTRING(@PostCode, NULLIF(PATINDEX('% GIR 0AA %', ' ' + @PostCode + ' '), 0), 7)

                )

       

END

-- RETURNS BIT

--AS

--BEGIN

--                    --SET @PostCode = IIF(ISNULL(dbo.fn_ExtractUKPostCode(@PostCode),'999'),dbo.fn_ExtractUKPostCode(@PostCode))

-- SET @sPostCode=UPPER(Replace(@sPostCode,' ',''))

-- SET @sPostCode= LEFT(@sPostCode,LEN(@sPostCode)-3)+' '+ Right(@sPostCode,3)

-- --declare @TestTab Table (postcode varchar(50) not null)

-- --Insert @TestTab values(@PostCode) --Valid

--   SET @sPostCode = CASE WHEN dbo.fn_ExtractUKPostCode(@sPostCode) IS NULL THEN '999' 

--                    ELSE dbo.fn_ExtractUKPostCode(@sPostCode) 

--    END

--      --SELECT  @PostCode = CASE 

--     --               WHEN dbo.fn_ExtractUKPostCode(PostCode) IS NULL THEN '999' 

--   --                 ELSE dbo.fn_ExtractUKPostCode(PostCode) END FROM   @TestTab

  

-- RETURN CASE

--      --            WHEN (PATINDEX('999',@sPostCode) = 0  AND 

-- -- PATINDEX('[A-Z][0-9] [0-9][A-Z][A-Z]', @sPostCode) = 0 AND  

-- --PATINDEX('[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]', @sPostCode) = 0 

-- -- ) THEN 0

--                     -- AANN NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     ---- AANA NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     -- ANN NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     -- AAN NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     -- ANA NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     --   AN NAA

--                     WHEN @sPostCode LIKE '[ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ]' THEN 1

--                     -- Not a valid postcode

-- --   Special case GIR 0AA

--                     WHEN @sPostCode LIKE 'GIR 0AA' THEN 1

--                     -- Current postcode prefixes

--                     WHEN   LEFT(@sPostCode, 2) NOT IN ('AB', 'AL', 'BA', 'BB', 'BD', 'BH', 'BL', 'BN', 'BR', 'BS', 'BT', 'CA', 'CB', 'CF', 'CH', 'CM', 'CO', 'CR', 'CT', 'CV', 'CW', 'DA', 'DD', 'DE', 'DG', 'DH', 'DL', 'DN', 'DT', 'DY', 'EC', 'EH', 'EN', 'EX', 'FK', 'FY', 'GL', 'GU', 'GY', 'HA', 'HD', 'HG', 'HP', 'HR', 'HS', 'HU', 'HX', 'IG', 'IM', 'IP', 'IV', 'JE', 'KA', 'KT', 'KW', 'KY', 'L', 'LA', 'LD', 'LE', 'LL', 'LN', 'LS', 'LU', 'ME', 'MK', 'ML', 'NE', 'NG', 'NN', 'NP', 'NR', 'NW', 'OL', 'OX', 'PA', 'PE', 'PH', 'PL', 'PO', 'PR', 'RG', 'RH', 'RM', 'SA', 'SE', 'SG', 'SK', 'SL', 'SM', 'SN', 'SO', 'SP', 'SR', 'SS', 'ST', 'SW', 'SY', 'TA', 'TD', 'TF', 'TN', 'TQ', 'TR', 'TS', 'TW', 'UB', 'WA', 'WC', 'WD', 'WF', 'WN', 'WR', 'WS', 'WV', 'YO', 'ZE') 

--                           OR LEFT(@sPostCode, 1) NOT IN ('B', 'E', 'G', 'L', 'N', 'S', 'W') THEN 0

 

       

--                     ELSE 0

--                END

--END

SQL FUNCTION TO VALIDATE DATE FORMAT

 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 @ 13:45 PM

-- Description:  Sql Function  to validate DATE FORMAT.

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

-- Modified By  :  Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Modify On -        

-- Input - Pass The DATE And Format Value Which You want to Validate 

-- OutPut - 

-- Return - 1 = Valid

---Return - 0 - Invalid    

-- SQL Script File Name - scr_05_Function_To_Validate_Date_Format_In_Staging.sql

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

 01_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat

--12 112 12 = yymmdd

--112 = yyyymmdd ISO 

--CONVERT(varchar,GETDATE(),112) --YYYYMMDD

--CONVERT(varchar,GETDATE(),108) --HH:MM:SS

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

--Check if a function exists on a ScribeStaging Database Before Creating - If exists Then Drop 

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

BEGIN

DROP FUNCTION [dbo].[fn_IsDateFormatCorrect]

END

GO

  CREATE FUNCTION dbo.fn_IsDateFormatCorrect

(

@vDate VARCHAR(80),

@vFormat VARCHAR(80)

)

RETURNS BIT

AS

BEGIN

DECLARE @Result BIT = 0

DECLARE @Dateformat INT

DECLARE @iDay INT = 0

DECLARE @iMonth INT = 0

DECLARE @iYear INT = 0

DECLARE @iCentury INT = 0

DECLARE @ihh INT = 0

DECLARE @imm INT = 0

DECLARE @iss INT = 0

    IF (@vDate LIKE '%[^.:0-9 ]%') -- Only Allow 0-9 and : and 1 Space

                    SET @Result = 1 

                ELSE

SELECT @DateFormat = CASE @vFormat

WHEN 'CCYYMM' THEN    101  -- Can Not Use ISDATE() - Select ISDATE('201512') AS Date ---CCYYMM -0

WHEN 'CCYYMMDD' THEN 102  -- Use ISDATE() - -- Select ISDATE('20151223') AS Date -- CCYYMMDD - 1

WHEN 'YYMMDD' THEN 12 -- Use ISDATE() - -- Select ISDATE('151231') AS Date -- YYMMDD - 1

WHEN 'YYYMMDD' THEN 104  --  Can Not Use ISDATE() -Select ISDATE('0151223') AS Date -- YYYMMDD -0 

WHEN 'YYYYMMDD' THEN 112  --   Use ISDATE() - -- Select ISDATE('20151231') AS Date -- YYYYMMDD -1 

WHEN 'YYYYMMDD HH:MM:SS' THEN 108 --   Use ISDATE() - Select ISDATE('20151231 23:33:54') AS Date -- YYYYMMDD HH:MM:SS - 1

WHEN 'DD.MM.YYYY' THEN 103 --         Use ISDATE() - Select ISDATE('01.01.2012') AS Date -- DD.MM.YYYY - 1

ELSE 100 

                         END

IF @DateFormat = 101 AND LEN(LTRIM(RTRIM(@vDate))) = 6 --- CCYYMM

BEGIN

            SET @iCentury =  LEFT(LTRIM(RTRIM(@vDate)),2)          -- CC

                                    SET @iYear =  SUBSTRING(LTRIM(RTRIM(@vDate)),3,2)   -- YY

Set @iMonth =  RIGHT(LTRIM(RTRIM(@vDate)),2)          -- MM

IF (@iCentury >=19 AND @iCentury <=20) AND (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12)

SET @Result =  0

ELSE

SET @Result =  1

END IF @DateFormat = 102 AND LEN(LTRIM(RTRIM(@vDate))) = 8 AND ISDATE (LTRIM(RTRIM(@vDate)))=1----CCYYMMDD

BEGIN

    SET @iCentury =  LEFT(LTRIM(RTRIM(@vDate)),2)          -- CC

                                    SET @iYear =  SUBSTRING(LTRIM(RTRIM(@vDate)),3,2)   -- YY

SET @iMonth =  SUBSTRING(LTRIM(RTRIM(@vDate)),5,2)   -- MM

Set @iDay =  RIGHT(LTRIM(RTRIM(@vDate)),2)         -- DD

IF (@iCentury >=19 AND @iCentury <=20) AND (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

END IF @DateFormat = 12  AND LEN(LTRIM(RTRIM(@vDate))) = 6  AND ISDATE (LTRIM(RTRIM(@vDate)))=1---YYMMDD

BEGIN

SET @iYear   =  LEFT(LTRIM(RTRIM(@vDate)),2)       -- YY

SET @iMonth  =  SUBSTRING(@vDate,3,2)              --  MM

                                    SET @iDay    =  RIGHT(LTRIM(RTRIM(@vDate)),2)        -- DD

IF (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

                                    

END IF @DateFormat = 104  AND LEN(LTRIM(RTRIM(@vDate))) = 7  ----YYYMMDD

BEGIN

SET @iYear   =  LEFT(LTRIM(RTRIM(@vDate)),3)        -- YYY

SET @iMonth  =  SUBSTRING(LTRIM(RTRIM(@vDate)),4,2) -- MM

                                    SET @iDay    =  RIGHT(LTRIM(RTRIM(@vDate)),2)        -- DD

IF (@iYear >=900 AND @iYear <=999) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

    END IF @DateFormat = 112  AND LEN(LTRIM(RTRIM(@vDate))) = 8  AND ISDATE (LTRIM(RTRIM(@vDate)))=1---YYYYMMDD

BEGIN

SET @iYear   =  LEFT(LTRIM(RTRIM(@vDate)),4)      -- YYY

SET @iMonth  =  SUBSTRING(LTRIM(RTRIM(@vDate)),5,2) -- MM

                                    SET @iDay    =  RIGHT(LTRIM(RTRIM(@vDate)),2)         -- DD

IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

END IF @DateFormat = 108 AND LEN(LTRIM(RTRIM(@vDate))) = 17 AND ISDATE (LTRIM(RTRIM(@vDate)))=1-- YYYYMMDD HH:MM:SS

BEGIN

  SET @iYear   =  LEFT(LTRIM(RTRIM(@vDate)),4)       -- YYYY

  SET @iMonth  =  SUBSTRING(LTRIM(RTRIM(@vDate)),5,2) -- MM

                                      SET @iDay    =  SUBSTRING(LTRIM(RTRIM(@vDate)),7,2) -- DD

  SET @ihh    =   SUBSTRING(LTRIM(RTRIM(@vDate)),9,3)    -- HH

  SET @imm    =   SUBSTRING(LTRIM(RTRIM(@vDate)),13,2)   -- MM

  SET @iss    =   RIGHT(LTRIM(RTRIM(@vDate)),2)          -- SS

  IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31) AND (@ihh >0 AND @ihh <=23) AND (@imm >0 AND @imm <=60) AND (@iss >0 AND @iss <=60)

SET @Result =  0

  ELSE

    SET @Result =  1

END IF @DateFormat = 103  AND LEN(LTRIM(RTRIM(@vDate))) = 10  AND ISDATE (LTRIM(RTRIM(@vDate)))=1---DD.MM.YYYY

BEGIN

SET @iYear   =  RIGHT(LTRIM(RTRIM(@vDate)),4)      -- YYY

SET @iMonth  =  SUBSTRING(LTRIM(RTRIM(@vDate)),4,2) -- MM

                                    SET @iDay    =  LEFT(LTRIM(RTRIM(@vDate)),2)         -- DD

IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

END

RETURN @Result

END

        

GO

SQL FUNCTION TO VALIDATE IF STRING CONTAINS NON PRINTABLE CHARS

 USE [ADN-TECHNOLOGIES-LIMITED-STAGING]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author: Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Created date: 17-May-2016 @ 17:23 PM

-- Description: Sql Function  to validate If String Contains Non Printable Chars.

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

-- Modified By  :  Vivek Nigam [ADN TECHNOLOGIES LIMITED]

-- Modify On -        

-- Input - Pass TEXT STRING You want to Validate 

-- OutPut - 

-- Return - 1 = Valid

---Return - 0 - Invalid    

-- SQL Script File Name - File Name - scr_04_Function_To_Unprintable_Chars_In_Staging.sql

-- This Function will be Find Non-Ascii Characters in One Varchar Column or Multiple Columns using SQL Server

-- This Function Will Created / excecated by Batch File Called - 03_Create_All_Functions_To_Validate_Cervical_Screening_Data_After_Importing_Into_Staging.bat

-- Here is a UDF I built to detect columns with extended ascii characters. It is quick and you can 

-- extended the character set you want to check. The second parameter allows you to switch between checking anything outside the standard character set or allowing an extended set:

--USAGE:

--select Address1 

--from PropertyFile_English

--where fn_ContainsNonPrintableChars(Address1, 1) = 1

--File Name - scr_04_Function_To_Unprintable_Chars_In_Staging.sql

-- Information Has Taken From http://web.itu.edu.tr/~sgunduz/courses/mikroisl/ascii.html & http://www.asciitable.com/

--NON PRINTABLE CHARACTERS 

--DEC HEX  CHARACTER (CODE) DEC HEX  CHARACTER (CODE) 

--0 0 NULL 16 10 DATA LINK ESCAPE (DLE) 

--1 1 START OF HEADING (SOH) 17 11 DEVICE CONTROL 1 (DC1) 

--2 2 START OF TEXT (STX) 18 12 DEVICE CONTROL 2 (DC2) 

--3 3 END OF TEXT (ETX) 19 13 DEVICE CONTROL 3 (DC3) 

--4 4 END OF TRANSMISSION (EOT) 20 14 DEVICE CONTROL 4 (DC4) 

--5 5 END OF QUERY (ENQ) 21 15 NEGATIVE ACKNOWLEDGEMENT (NAK) 

--6 6 ACKNOWLEDGE (ACK) 22 16 SYNCHRONIZE (SYN) 

--7 7 BEEP (BEL) 23 17 END OF TRANSMISSION BLOCK (ETB) 

--8 8 BACKSPACE (BS) 24 18 CANCEL (CAN) 

--9 9 HORIZONTAL TAB (HT) 25 19 END OF MEDIUM (EM) 

--10 A LINE FEED (LF) 26 1A SUBSTITUTE (SUB) 

--11 B VERTICAL TAB (VT) 27 1B ESCAPE (ESC) 

--12 C FF (FORM FEED) 28 1C FILE SEPARATOR (FS) RIGHT ARROW 

--13  D CR (CARRIAGE RETURN) 29 1D GROUP SEPARATOR (GS) LEFT ARROW 

--14 E SO (SHIFT OUT) 30 1E RECORD SEPARATOR (RS) UP ARROW 

--15 F SI (SHIFT IN) 31 1F UNIT SEPARATOR (US) DOWN ARROW 

----------------EXTENDED ASCII CHARACTERS 

----------------DEC HEX 

----------------CHARACTER 

---------------- DEC HEX CHARACTER DEC HEX CHARACTER 

---------------- 128  0x80 € 171  0xAB « 214  0xD6 Ö 

---------------- 129  0x81   172  0xAC ¬ 215  0xD7 × 

---------------- 130  0x82 ‚ 173  0xAD ­ 216  0xD8 Ø 

---------------- 131  0x83 ƒ 174  0xAE ® 217  0xD9 Ù 

---------------- 132  0x84 „ 175  0xAF ¯  218  0xDA Ú 

---------------- 133  0x85 … 176  0xB0 °  219  0xDB Û 

---------------- 134  0x86 †  177  0xB1 ± 220  0xDC Ü 

---------------- 135  0x87 ‡  178  0xB2 ² 221  0xDD Ý 

---------------- 136  0x88 ˆ 179  0xB3 ³ 222  0xDE Þ 

---------------- 137  0x89 ‰  180  0xB4 ´  223  0xDF ß 

---------------- 138  0x8A Š 181  0xB5 µ 224  0xE0 à 

---------------- 139  0x8B ‹ 182  0xB6 ¶  225  0xE1 á 

---------------- 140  0x8C Œ 183  0xB7 ·  226  0xE2 â 

---------------- 141  0x8D   184  0xB8 ¸  227  0xE3 ã 

---------------- 142  0x8E Ž 185  0xB9 ¹ 228  0xE4 ä 

---------------- 143  0x8F   186  0xBA º 229  0xE5 å 

---------------- 144  0x90   187  0xBB » 230  0xE6 æ 

---------------- 145  0x91 ‘ 188  0xBC ¼  231  0xE7 ç 

---------------- 146  0x92 ’ 189  0xBD ½  232  0xE8 è 

---------------- 147  0x93 “ 190  0xBE ¾  233  0xE9 é 

---------------- 148  0x94 ” 191  0xBF ¿  234  0xEA ê 

---------------- 149  0x95 • 192  0xC0 À  235  0xEB ë 

---------------- 150  0x96 – 193  0xC1 Á  236  0xEC ì 

---------------- 151  0x97 — 194  0xC2   237  0xED í 

---------------- 152  0x98 ˜ 195  0xC3 à 238  0xEE î 

---------------- 153  0x99 ™  196  0xC4 Ä  239  0xEF ï 

---------------- 154  0x9A š 197  0xC5 Å  240  0xF0 ð 

---------------- 155  0x9B › 198  0xC6 Æ  241  0xF1 ñ 

---------------- 156  0x9C œ 199  0xC7 Ç  242  0xF2 ò 

---------------- 157  0x9D   200  0xC8 È  243  0xF3 ó 

---------------- 158  0x9E ž 201  0xC9 É  244  0xF4 ô 

---------------- 159  0x9F Ÿ 202  0xCA Ê  245  0xF5 õ 

---------------- 160  0xA0    203  0xCB Ë  246  0xF6 ö 

---------------- 161  0xA1 ¡  204  0xCC Ì  247  0xF7 ÷ 

---------------- 162  0xA2 ¢ 205  0xCD Í  248  0xF8 ø 

---------------- 163  0xA3 £ 206  0xCE Π 249  0xF9 ù 

---------------- 164  0xA4 ¤ 207  0xCF Ï  250  0xFA ú 

---------------- 165  0xA5 ¥ 208  0xD0 Р 251  0xFB û 

---------------- 166  0xA6 ¦ 209  0xD1 Ñ  252  0xFC ü 

---------------- 167  0xA7 §  210  0xD2 Ò  253  0xFD ý 

---------------- 168  0xA8 ¨  211  0xD3 Ó  254  0xFE þ 

---------------- 169  0xA9 © 212  0xD4 Ô  255  0xFF ÿ 

---------------- 170  0xAA ª 213  0xD5 Õ         

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

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

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

BEGIN

DROP FUNCTION [dbo].fn_ContainsNonPrintableChars

END

GO

  CREATE FUNCTION dbo.fn_ContainsNonPrintableChars

(

@String nvarchar(4000),

@CheckExtendedCharset bit

)

RETURNS BIT

as

begin

declare @pos int = 0;

declare @char varchar(1);

declare @return bit = 0;

while @pos < len(@string)

begin

select @char = substring(@string, @pos, 1)

--if ascii(@char) < 32 or ascii(@char) > 126  --NON PRINTABLE CHARACTERS & EXTENDED ASCII CHARACTERS

if ascii(@char) < 32 or ascii(@char) > 127 --NON PRINTABLE CHARACTERS

begin

if @checkExtendedCharset = 1 ---- EXTENDED ASCII CHARACTERS

begin

--if ascii(@char) NOT IN (

--                                   9,124,130,138,142,146,150,154,

--    158,160,163,170,176,180,181,183,184,

--    185,186,192,193,194,195,196,197,

--    199,200,201,202,203,204,205,206,

--    207,209,210,211,212,213,214,216,

--    217,218,219,220,221,223,224,225,

--    226,227,228,229,230,231,232,233,

--    234,235,236,237,238,239,240,241,

--    242,243,244,245,246,248,249,250,

--    251,252,253,254,255

   

   --)

if ascii(@char) NOT IN (

9,124,128,129,130,131,132,133,134,

135,136,137,138,139,140,141,

142,143,144,145,146,147,148,

149,150,151,152,153,154,155,

157,158,159,160,161,162,163,

164,165,166,167,168,176,177,

178,180,181,182,183,184,185,

186,187,188,189,190,191,192,

193,194,195,196,197,198,199,

200,201,202,203,204,205,206,

207,209,210,211,212,213,214,

215,216,217,218,219,220,221,

222,223,224,225,226,227,228,

229,230,231,232,233,234,235,

236,237,238,239,240,244,245,

246,248,249,250,251,252,253,

254,255

)

begin

select @return = 1;

select @pos = (len(@string) + 1)

end

else

begin

select @pos = @pos + 1

end

end

else

begin

select @return = 1;

select @pos = (len(@string) + 1)    

end

end

else

begin

select @pos = @pos + 1

end

end

return @return;

end

GO

SQL FUNCTION TO VALIDATE DATE FORMAT

 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 @ 13:45 PM

-- Description: Sql Function  to validate DATE FORMAT.

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

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

-- Modify On -        

-- Input - Pass The DATE And Format Value Which You want to Validate 

-- OutPut - 

-- Return - 1 = Valid

---Return - 0 - Invalid    

-- SQL Script File Name - scr_03_Function_To_Validate_Date_Format_In_Staging.sql

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

 01_Create_All_Functions_To_Validate_Data_After_Importing_Into_Staging.bat

--12 112 12 = yymmdd

--112 = yyyymmdd ISO 

--CONVERT(varchar,GETDATE(),112) --YYYYMMDD

--CONVERT(varchar,GETDATE(),108) --HH:MM:SS

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

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

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

BEGIN

DROP FUNCTION [dbo].[fn_IsDateFormatCorrect]

END

GO

  CREATE FUNCTION dbo.fn_IsDateFormatCorrect

(

@vDate VARCHAR(80),

@vFormat VARCHAR(80)

)

RETURNS BIT

AS

BEGIN

DECLARE @Result BIT = 0

DECLARE @Dateformat INT

DECLARE @iDay INT = 0

DECLARE @iMonth INT = 0

DECLARE @iYear INT = 0

DECLARE @iCentury INT = 0

DECLARE @ihh INT = 0

DECLARE @imm INT = 0

DECLARE @iss INT = 0

    IF (@vDate LIKE '%[^.:0-9 ]%') -- Only Allow 0-9 and : and 1 Space

                    SET @Result = 1 

                ELSE

SELECT @DateFormat = CASE @vFormat

WHEN 'CCYYMM' THEN    101  -- Can Not Use ISDATE() - Select ISDATE('201512') AS Date ---CCYYMM -0

WHEN 'CCYYMMDD' THEN 102  -- Use ISDATE() - -- Select ISDATE('20151223') AS Date -- CCYYMMDD - 1

WHEN 'YYMMDD' THEN 12 -- Use ISDATE() - -- Select ISDATE('151231') AS Date -- YYMMDD - 1

WHEN 'YYYMMDD' THEN 104  --  Can Not Use ISDATE() -Select ISDATE('0151223') AS Date -- YYYMMDD -0 

WHEN 'YYYYMMDD' THEN 112  --   Use ISDATE() - -- Select ISDATE('20151231') AS Date -- YYYYMMDD -1 

WHEN 'YYYYMMDD HH:MM:SS' THEN 108 --   Use ISDATE() - Select ISDATE('20151231 23:33:54') AS Date -- YYYYMMDD HH:MM:SS - 1

WHEN 'DD.MM.YYYY' THEN 103 --         Use ISDATE() - Select ISDATE('01.01.2012') AS Date -- DD.MM.YYYY - 1

ELSE 100 

                         END

IF @DateFormat = 101 AND LEN(LTRIM(RTRIM(@vDate))) = 6 --- CCYYMM

BEGIN

            SET @iCentury =  LEFT(LTRIM(RTRIM(@vDate)),2)          -- CC

                                    SET @iYear =  SUBSTRING(LTRIM(RTRIM(@vDate)),3,2)   -- YY

Set @iMonth =  RIGHT(LTRIM(RTRIM(@vDate)),2)          -- MM

IF (@iCentury >=19 AND @iCentury <=20) AND (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12)

SET @Result =  0

ELSE

SET @Result =  1

END IF @DateFormat = 102 AND LEN(LTRIM(RTRIM(@vDate))) = 8 AND ISDATE (LTRIM(RTRIM(@vDate)))=1----CCYYMMDD

BEGIN

    SET @iCentury =  LEFT(LTRIM(RTRIM(@vDate)),2)          -- CC

                                    SET @iYear =  SUBSTRING(LTRIM(RTRIM(@vDate)),3,2)   -- YY

SET @iMonth =  SUBSTRING(LTRIM(RTRIM(@vDate)),5,2)   -- MM

Set @iDay =  RIGHT(LTRIM(RTRIM(@vDate)),2)         -- DD

IF (@iCentury >=19 AND @iCentury <=20) AND (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

END IF @DateFormat = 12  AND LEN(LTRIM(RTRIM(@vDate))) = 6  AND ISDATE (LTRIM(RTRIM(@vDate)))=1---YYMMDD

BEGIN

SET @iYear   =  LEFT(LTRIM(RTRIM(@vDate)),2)       -- YY

SET @iMonth  =  SUBSTRING(@vDate,3,2)              --  MM

                                    SET @iDay    =  RIGHT(LTRIM(RTRIM(@vDate)),2)        -- DD

IF (@iYear >=0 AND @iYear <=99) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

                                    

END IF @DateFormat = 104  AND LEN(LTRIM(RTRIM(@vDate))) = 7  ----YYYMMDD

BEGIN

SET @iYear   =  LEFT(LTRIM(RTRIM(@vDate)),3)        -- YYY

SET @iMonth  =  SUBSTRING(LTRIM(RTRIM(@vDate)),4,2) -- MM

                                    SET @iDay    =  RIGHT(LTRIM(RTRIM(@vDate)),2)        -- DD

IF (@iYear >=900 AND @iYear <=999) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

    END IF @DateFormat = 112  AND LEN(LTRIM(RTRIM(@vDate))) = 8  AND ISDATE (LTRIM(RTRIM(@vDate)))=1---YYYYMMDD

BEGIN

SET @iYear   =  LEFT(LTRIM(RTRIM(@vDate)),4)      -- YYY

SET @iMonth  =  SUBSTRING(LTRIM(RTRIM(@vDate)),5,2) -- MM

                                    SET @iDay    =  RIGHT(LTRIM(RTRIM(@vDate)),2)         -- DD

IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

END IF @DateFormat = 108 AND LEN(LTRIM(RTRIM(@vDate))) = 17 AND ISDATE (LTRIM(RTRIM(@vDate)))=1-- YYYYMMDD HH:MM:SS

BEGIN

  SET @iYear   =  LEFT(LTRIM(RTRIM(@vDate)),4)       -- YYYY

  SET @iMonth  =  SUBSTRING(LTRIM(RTRIM(@vDate)),5,2) -- MM

                                      SET @iDay    =  SUBSTRING(LTRIM(RTRIM(@vDate)),7,2) -- DD

  SET @ihh    =   SUBSTRING(LTRIM(RTRIM(@vDate)),9,3)    -- HH

  SET @imm    =   SUBSTRING(LTRIM(RTRIM(@vDate)),13,2)   -- MM

  SET @iss    =   RIGHT(LTRIM(RTRIM(@vDate)),2)          -- SS

  IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31) AND (@ihh >0 AND @ihh <=23) AND (@imm >0 AND @imm <=60) AND (@iss >0 AND @iss <=60)

SET @Result =  0

  ELSE

    SET @Result =  1

END IF @DateFormat = 103  AND LEN(LTRIM(RTRIM(@vDate))) = 10  AND ISDATE (LTRIM(RTRIM(@vDate)))=1---DD.MM.YYYY

BEGIN

SET @iYear   =  RIGHT(LTRIM(RTRIM(@vDate)),4)      -- YYY

SET @iMonth  =  SUBSTRING(LTRIM(RTRIM(@vDate)),4,2) -- MM

                                    SET @iDay    =  LEFT(LTRIM(RTRIM(@vDate)),2)         -- DD

IF (@iYear >=1900 AND @iYear <=2099) AND (@iMonth >=1 AND @iMonth <=12) AND (@iDay >=1 AND @iDay <=31)

SET @Result =  0

ELSE

SET @Result =  1

END

RETURN @Result

END

        

GO

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

SAP Table KNB1- Extract KNB1 Customer Master (Company Code) Data From SAP Table to CSV Using ABAP Program

 Extract Data From SAP to CSV Using ABAP Program For SAP Table KNB1 

Requirement - Extract Customer Master (Company Code)  Data From SAP KNB1 Table For All Available Company Code(s) / Cost Center(s).

Filed(s) Name -  KUNNR, ZWELS, ZTERM

Remark - How To Use INNER JOIN Table Join OR Where Clause In ABAP Program 

*&---------------------------------------------------------------------*

*& Report  Z_KNB1_COMPANY_CODE

*&---------------------------------------------------------------------*

*&*& Extract SAP Data For Customer Master (Company Code) SAP TABLE KNB1 Into cvs File Called KNB1.csv

*&---------------------------------------------------------------------*

*& Developed By -   SAI CONSULTANCY SERVICES LIMITED

*& Date - 12/04/2024

*&---------------------------------------------------------------------*

*&--KUNNR        ZWELS  ZTERM

*&---------------------------------------------------------------------*

REPORT  Z_KNB1_COMPANY_CODE.

 

type-pools:TRUXS.

data: begin of itab occurs 0,

      kunnr like knb1-kunnr,

      zwels like knb1-zwels,

      zterm like knb1-zterm,

      bukrs like knb1-bukrs,

      end of itab.

data:  itab1 type TRUXS_T_TEXT_DATA.

select  knb1~kunnr

         knb1~zwels

         knb1~zterm

         knb1~bukrs

         from KNB1

         into table itab

         where knb1~bukrs = 'UK01'.

 

CALL FUNCTION 'SAP_CONVERT_TO_CSV_FORMAT'

EXPORTING

   I_FIELD_SEPERATOR          = ','

  TABLES

    I_TAB_SAP_DATA             = itab

CHANGING

   I_TAB_CONVERTED_DATA       =  itab1

EXCEPTIONS

   CONVERSION_FAILED          = 1

   OTHERS                     = 2

          .

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

 

   CALL FUNCTION 'GUI_DOWNLOAD'

    EXPORTING

      filename = 'C:\tmp\KNB1.csv'

    TABLES

      data_tab = itab1

    EXCEPTIONS

      OTHERS   = 1.