Pulling Minimum Lengths 3

[Swi]

Hi,

I have the below query. My thought was the part at the end would only select records that had a +4 zip code (12345-1234), however this does not seem to work. Any ideas?

Thanks.

"SELECT Min(Len([NAME])) AS [NAME], " & _
            "Min(Len([ADDR2])) As [ADDR2], Min(Len([ADDR1])) As [ADDR1], " & _
            "Min(Len([CITY ST ZIP])) As [CITY ST ZIP], Min(Len([Salutation])) As [Salutation] " & _
            "FROM [Test.csv] WHERE LEFT(RIGHT([CITY ST ZIP],5),1) = '-'"

 

[gmmastros]

try this

Where [CITY ST ZIP] like '%[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%'

 

[dhookom]

You never said why you think the expression didn’t seem to work or provide examples.

Most of us recommend three separate fields for City, State, and Zip. Maybe you have no control over this.

 

[Swi]

I will give that a shot. After I posted I think I may have found the issue but had to leave for the day. I will check it Monday and report back. Thanks.

 

[Chriss Miller]

I think you have trailing spaces in the field, so the thought of having a - in the left 1 char of the rightmost 5 chars is wrong, you first need to trim off spaces (and other whitespace, if there may be tabs, for example, even if only accidentally).

Gammastros LIKE expression (pay close attention to the single - outside square bracket expresions for digits) will give you an even better filter of only rows that conform to ddddd-dddd when d stands for a digit. The % allow any further characters before and after. which is crucial, as you likely have trailing spaces. You could avoid them when you'd make it a varchar field and trim before you store an address, I still think there would be other ZIPs that could have letters in international addresses, you'll just not be interested in them.

To follow up on the advice to store city, street and ZIP in three fields, if you know they're in that order you could use the SPLIT() function, but this is also not straight forward, as all of the three parts can have spaces in them with international addresses, so you likely get more than 3 parts from a split and have to decide which parts to combine. It's even less viable with international addresses that can vary from this pattern overall.

 

[Swi]

Hi,

Agree about the split fields however this is the output data format that is needed for the machinery it is running on so I am stuck with this format. I did get this working earlier today. It was spacing so I just added the TRIM and all was good. Thanks all.

 

[Chriss Miller]

Glad you got that going. Still a normalized data can feed anything that needs the address composed, you can easily write a query putting the single values together after tidying up the data.