Developers
Store a UPCS Code in Your Database
Column type, length, indexing and loose format validation for storing UPCS codes in SQL, plus why a lookup beats relying on a regex alone.
Updated 14 October 2026 · 2 min read
A UPCS code is a short string like PK-1A2B-3C4D: a two-letter country prefix followed by two blocks of letters and digits. Storing it is simple, but a few decisions make it robust.
Column type
A code is 12 characters including hyphens. Use a string column with some headroom:
ALTER TABLE orders
ADD COLUMN upcs_code VARCHAR(16) NULL,
ADD COLUMN upcs_latitude DECIMAL(9,6) NULL,
ADD COLUMN upcs_longitude DECIMAL(9,6) NULL;
CREATE INDEX idx_orders_upcs_code ON orders (upcs_code);
Make the column nullable: the code complements the address, and not every customer will choose a location. Store the code with the order; coordinates are a useful companion, see storing coordinates in a database.
Normalise on the way in
Trim whitespace and uppercase the value before saving, so pk-1a2b-3c4d and PK-1A2B-3C4D are one value. Keep the hyphens, as they are part of the displayed code.
Loose validation, then confirm
A pattern like this catches typos at the form:
$looksValid = preg_match('/^[A-Z]{2}-[A-Z0-9]{4}-[A-Z0-9]{4}$/', $code) === 1;
Treat the regex as a convenience, not a guarantee. The format is documented as CC-XXXX-XXXX, but the only way to know that a code is real is to look it up through the API. A code that passes the regex may not exist, and a future code could differ in detail from your assumption.
Do not make it the only address
Keep the postal address columns. A UPCS code is a complement; it is not an official postcode and does not replace national postal systems. Couriers, invoices and tax systems still expect a normal address.
Related
Frequently asked questions
What column type should I use?
A short string such as VARCHAR(16) is enough for the CC-XXXX-XXXX format. Leave a little headroom rather than using a tight fixed length.
Is a regex enough to validate a code?
It catches typos, but only a lookup confirms that the code is real. Use both.