Zip Code SQL Query

activities
studio
string

#1

I have a database that contains 5 digit zip codes for the united states. The data that is being used to execute the query isn’t always consistently formatted. For example, I’ve had 12345-4564 and 123456789 input into the zip code field. I’m trying to trim this down to only the first 5 digits provided to provide accurate results without having to do a secondary query. Can anyone help me with this??


#2

Hello @tpeercy,

Is your requirement only to retrieve the data and trim it for your use or you also want to update the database table after trim?

In first case, you can try a SQL query like this:
SELECT SUBSTRING(ZipCode, 1, 5) FROM yourTable;

In second case, you will have to use the same function but with Update query.

Let me know if that helps.


#3

Hello,

My current query is below, I’m trying to trim the data that is input. So an example of this would be the provided data being 12345678, but I need it to ONLY query for the first 5, in this case 12345.

“Select * from Zipcodes where Zipcode = '” + shipmentData(“PostalCode”) + “’”

In this case Zipcodes is the table name, Zipcode is a column header, and shipmentData(“PostalCode”) is a key value that is populated by information obtained from the system.


#4

Alright. That means the column Zipcode that’s present in your database table already has 5 digits. You want to trim the input variable. You can use the below query.
“Select * from Zipcodes where Zipcode = SUBSTRING(’” + shipmentData(“PostalCode”) + “’”,1,5)

OR you can also try getting substring of the variable first as given below:

“Select * from Zipcodes where Zipcode = '” + shipmentData(“PostalCode”).Substring(0,5) + “’"

Please let me know if this helps. :slight_smile:


#5

Thank you so much!! The first one was giving me a syntax error for some reason, so I went ahead and tried the second one and it works like a charm!

I really appreciate it!