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??
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.
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.
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.
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!