What I’m trying to do is determine (Using Teradata SQL) if a person’s zip code has accidently been put on an address line. I’ve looked on various forums and I can’t find any similar questions.
Ultimately, I would want to write something like:
Where address_line_1 like '%[0-9][0-9][0-9][0-9][0-9]%'
Any ideas?
Target database is Teradata 13.x
Advertisement
Answer
If you want to inspect the entire column to see if it contains only a ZIP code, you might try something like this:
where address_line_1 between '00000' and '99999'
But if you are thinking of searching the entire string for any occurrence of five consecutive digits, that would not be a good test anyway. For example, the following would be a perfectly valid mailing address:
28305 Southwest Main Street
Doing validity checks after data has been loaded is difficult; such a task should really be performed during the load process.