How to filter out data with special character in vectorwise

Question

There is a column named Prod_code in my product table. I need to select only valid prod_code from product table and load it into another table. valid prod_code are the codes which don't have any special characters in it.

VALID prod_code: WER1234, ASD1345

INVALID prod_code: ABC$123,LPS????,$$$ (which I need to check and filter out).
How can I achieve this?

Src list of prod code

WER1234 
ASD1345
ABC$123
LPS????
$$$

target list of prod code

WER1234
ASD1345

Answer 1

This is pretty hideous but it does the job. It uses the LIKE predicate to reject unacceptable strings. You can probably also use the SIMILAR TO predicate to specify acceptable strings but I would guess that LIKE is faster.

select prod_code from products
where prod_code not like '%\['+x'00'+'-'+x'2f'+','+
                               x'3a'+'-'+x'40'+','+
                               x'5d'+'-'+x'7f'+'\]%' escape '\'
 and prod_code not like '%\%'
 and prod_code not like '%[%'