Guest User

Untitled

a guest
Mar 17th, 2018
95
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.22 KB | None | 0 0
  1. use tprschools
  2. go
  3. set transaction isolation level read uncommitted
  4. go
  5. declare @ascii_chars varchar(150)
  6. declare @extended_ascii varchar(500)
  7. declare @ascii_code int
  8. set @ascii_code = 0
  9. set @ascii_chars = ''
  10. set @extended_ascii = ''
  11.  
  12. -- // Construct list of ascii chars:
  13. while (@ascii_code <= 255)
  14. begin
  15. if (@ascii_code <= 127)
  16. begin
  17. set @ascii_chars = @ascii_chars + char(@ascii_code)
  18. end else begin
  19. set @extended_ascii = @extended_ascii + char(@ascii_code)
  20. end
  21.  
  22. set @ascii_code = @ascii_code + 1
  23. end
  24.  
  25.  
  26. print 'ascii chars (len='+cast(len(@ascii_chars) as varchar)+'): ''' + @ascii_chars + ''''
  27. -- // Escape values that are reserved chars in SQL like patterns:
  28. set @ascii_chars = replace(@ascii_chars,'\','\\')
  29. set @ascii_chars = replace(@ascii_chars,'^','\^')
  30. set @ascii_chars = replace(@ascii_chars,']','\]')
  31. set @ascii_chars = replace(@ascii_chars,'-','\-')
  32. set @ascii_chars = replace(@ascii_chars,'[','\[')
  33. set @ascii_chars = replace(@ascii_chars,'_','\_')
  34. set @ascii_chars = replace(@ascii_chars,'%','\%')
  35. set @ascii_chars = replace(@ascii_chars,'''','''''')
  36.  
  37. print 'ascii chars (len='+cast(len(@ascii_chars) as varchar)+'): ''' + @ascii_chars + ''''
  38. print '@extended ascii chars (len='+cast(len(@extended_ascii) as varchar)+'): ''' + @extended_ascii + ''''
  39.  
  40.  
  41.  
  42. -- //
  43. -- // First prove @ascii_char is properly escaped:
  44. select distinct
  45. pattern, [desc]
  46. , case when f.pattern like '%[^'+@ascii_chars+']%' escape '\' then 'NOT 7-bit ASCII' end '(excl. match)'
  47. , case when f.pattern like '%['+@extended_ascii+']%' escape '\' then 'NOT 7-bit ASCII' end '(incl. match)'
  48. , case when patindex('%['+@extended_ascii+']%', f.pattern) > 0 then 'NOT 7-bit ASCII' end '(patindex match)'
  49. , patindex('%['+@extended_ascii+']%', f.pattern) as pat_index
  50. from
  51. (
  52. select 'normal text' pattern, 'normal, ascii chars' 'desc'
  53. union select '‚', 'a NON-ASCII CHAR'
  54. union select '[', 'left sq. bracket'
  55. union select ']', 'right sq. bracket'
  56. union select '_', 'underscore'
  57. union select '-', 'minus sign is a special char when using like ''%[]%'''
  58. union select '%', 'percent'
  59. union select '^', 'caret'
  60. union select '''', 'single quote'
  61. union select '\', 'what we are using as the escape char'
  62. ) f
  63.  
  64.  
  65. -- //
  66. -- // find values w/ non-7-bit-ascii, also include sneak-peek of text surrounding first occurance of the value:
  67. select distinct
  68. pattern, [desc]
  69. , case when f.pattern like '%[^'+@ascii_chars+']%' escape '\' then 'NOT 7-bit ASCII' end '(excl. match)'
  70. , case when f.pattern like '%['+@extended_ascii+']%' escape '\' then 'NOT 7-bit ASCII' end '(incl. match)'
  71. , case when patindex('%['+@extended_ascii+']%', f.pattern) > 0 then 'NOT 7-bit ASCII' end '(patindex match)'
  72. , patindex('%['+@extended_ascii+']%', f.pattern) as first_index_of_non_ascii
  73. , substring(f.pattern,patindex('%['+@extended_ascii+']%', f.pattern),1) first_non_ascii_char
  74. , ascii(substring(f.pattern,patindex('%['+@extended_ascii+']%', f.pattern),1)) matching_char_ascii_code
  75. , patindex('%['+@extended_ascii+']%', f.pattern) index_of_first_matching_char
  76. , substring(f.pattern,patindex('%['+@extended_ascii+']%', f.pattern)-5,10) surrounding_text
  77. from
  78. (
  79. select 'normal text' pattern, 'normal, ascii chars' 'desc'
  80. union select '[', 'left sq. bracket'
  81. union select ']', 'right sq. bracket'
  82. union select '_', 'underscore'
  83. union select '-', 'minus sign is a special char when using like ''%[]%'''
  84. union select '%', 'percent'
  85. union select '^', 'caret'
  86. union select '''', 'single quote'
  87. union select 'some normal text, basic ascii charset', 'you shouldn''t see this in the query results'
  88. -- // none of above rows should be returned, but the remaining 5 should be in the result set:
  89. union select ' consist of a ½ hour long ses', 'random text sample'
  90. union select 'ublic service ¡V whether that', 'random text sample'
  91. union select 'uality Matters© ad well as by', 'random text sample'
  92. union select 'l store front ¬electronic sub', 'random text sample'
  93. union select 'This one has two non-ascii chars. The query will only show the first one. l store front ¬electronic sub. It has major issues though with 1080i HDTV. Interlaced material is not properly deinterlaced and this is only under VMR9. l store front ¬electronic sub.', 'random text sample'
  94.  
  95. ) f
  96. where
  97. f.pattern like '%['+@extended_ascii+']%'
Add Comment
Please, Sign In to add comment