CODEWITHSUNDEEP
sqlstringwildcardnumericteradata
Lee_Str
Lee_Str

Reputation: 3496

How do I search for a five digit number in a string column?

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

Upvotes: 2

Views: 9515

Answers (3)

Guest
Guest

Reputation: 1

where address_line_1 between '00000' and '99999' would not work if there are four-digit numbers in your address_line_1 because it will pick them up Where address_line_1 like '%[0-9][0-9][0-9][0-9][0-9]%' would be a better solution.

Upvotes: 0

BellevueBob
BellevueBob

Reputation: 9618

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.

Upvotes: 6

adarsh
adarsh

Reputation: 6978

Find all the entries that match this regex [^0-9][0-9][0-9][0-9][0-9][0-9][^0-9]

As this will find numbers in some text that are exactly 5 digits long, assuming that's the definition of a zipcode.

Upvotes: 1

Related Questions