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