ianbrown76
Technical User
Hi
I create a file that contains numerous columns with a Product Description. These can contain spurious characters such as tabs and a plus above a minus etc. This file is rejected in the next process until I remove these records.
In the SQL I can keep on adding characters to exclude using the Like function e.g. where Product_Description like '%%' though I was hoping to be cleverer about this.
Looking at ASCII codes realistically I need to exclude those less than 32 and above 122.
I can identify products by running the SQL below looking at each character in the field, i.e. twenty times as it's Char(20):-
Sel Product_Description
FROM product
WHERE ascii(substr(product_description,7,1)) < 32
or ascii(substr(product_description,7,1)) > 122
Is there a way of identifying descriptions with these characters without performing it at substring level? If I could find the Position of a character in Product_Description where Ascii code > 122 etc then it would save me writing the code twenty times for each occurrance of Product_Description.
Does anybody have any suggestions and the syntax to help me identify these fields containing these characters?
Any help would be most appreciated.
Ian
I create a file that contains numerous columns with a Product Description. These can contain spurious characters such as tabs and a plus above a minus etc. This file is rejected in the next process until I remove these records.
In the SQL I can keep on adding characters to exclude using the Like function e.g. where Product_Description like '%%' though I was hoping to be cleverer about this.
Looking at ASCII codes realistically I need to exclude those less than 32 and above 122.
I can identify products by running the SQL below looking at each character in the field, i.e. twenty times as it's Char(20):-
Sel Product_Description
FROM product
WHERE ascii(substr(product_description,7,1)) < 32
or ascii(substr(product_description,7,1)) > 122
Is there a way of identifying descriptions with these characters without performing it at substring level? If I could find the Position of a character in Product_Description where Ascii code > 122 etc then it would save me writing the code twenty times for each occurrance of Product_Description.
Does anybody have any suggestions and the syntax to help me identify these fields containing these characters?
Any help would be most appreciated.
Ian
