2

Is there a way to query a table for a given value in all of it's column values in Netezza. Scenario is to make sure no invalid characters should be seen in our entire table.

Ascii values to be ignored are 

($i < 32 ) Or ( $i > 32 &&  $i < 45 )
 Or ( $i >= 58 &&  $i < 65 ) 
or ( $i > 90 &&  $i < 122 ) || ( $i > 122 )

Able to get the result using Unix script , but it's taking nearly 30 mins to scan 64 columns, 61000 records.

Better Netezza Sql approach is recommended.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
user3250280
  • 29
  • 1

1 Answers1

2

If you have the Netezza SQL extensions toolkit installed, You can solve this problem using regex.

select sql_functions..regexp_extract('546asdasdÇ','[\x20-\x7E]+')

with \x20 being the starting hex code and \x7E the end. Refer to this webpage and make any adjustments.

Niederee
  • 4,155
  • 25
  • 38
  • Here is a [good link](http://stackoverflow.com/questions/4345621/php-regex-to-allow-letters-and-numbers-only) for reference on another stack question – weeksdev Feb 20 '14 at 22:29