1

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

Community
  • 1
  • 1
AMPorter
  • 31
  • 2
  • 2
  • 4
  • 1
    give me the sample code – King of kings Apr 08 '14 at 11:24
  • Please post your code (not just link to another post) and explain what "does not seem to work" means. –  Apr 08 '14 at 13:04
  • The post you link to uses regexes, a feature which DB2 does not have. You will have to come up with a different solution. –  Apr 08 '14 at 13:16
  • I have tried to use the below. – AMPorter Apr 08 '14 at 13:59
  • I have tried to use:- `SELECT * FROM PAYMENT WHERE NOT xmlcast(xmlquery('fn:matches($s, "^[a-zA-Z0-9 ]*[0-9][a-zA-Z0-9 ]*$")' passing LONG_NAME AS "s") AS int)=0`. I have used the xmlcast before and it worked fine with another regular expression to determine if the first 2 characters are a letter and the second 2 are a number using `"^[a-zA-Z]{2}[0-9]{2}"` When i run the above query all records are returned including the records that contain no special characters or spaces, both of which should not be returned – AMPorter Apr 08 '14 at 14:04
  • Possible duplicate of http://stackoverflow.com/questions/17462802/how-to-find-special-characters-in-db2/17469776#17469776 – mustaccio Apr 08 '14 at 15:12
  • Why is your regexp looking for a digit in the middle? Why not just `"^[a-zA-Z0-9 ]*$"` ? – Turophile Apr 10 '14 at 07:13

1 Answers1

5

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') <> '';
Josh Hull
  • 1,723
  • 1
  • 16
  • 24
  • If you do it this way, the third parameter of TRANSLATE() should only list the characters that you want to be accepted. The characters at the end, i.e., `!@#$%^&*()-=+/\{}[];:.,<>? `, should probably be removed from the list. – user2338816 Apr 12 '14 at 02:36