CODEWITHSUNDEEP
sqldb2
AMPorter
AMPorter

Reputation: 31

Select records containing non alphanumeric characters in DB2

I am trying to create a query within DB2 that will select all records which contain non alphanumeric characters within a given field. I have tried the code in the post but it does not seem to work. The information i am needing to search is address information, therefore spaces will be acceptable i.e.

Data Example - 10 The High Street - Good, therefore dont show in report 10 The High-Street - Bad, therefore show in report

Any help will be most appreciated

Upvotes: 1

Views: 14606

Answers (1)

Josh Hull
Josh Hull

Reputation: 1783

One option is to translate all of the known printable characters to an empty space, and test whether the result is greater than a single space. 

select KEY_VALUE
from TABLE
where TRANSLATE(CHAR_COLUMN,'','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890') <> '';

Upvotes: 5

Related Questions