Swiggy: Saved Addresses Without a Valid Pincode

Easy
951.

Swiggy: Saved Addresses Without a Valid Pincode

EasyStrings

MySQL

Orders sent to an address with a broken pincode get routed to the wrong delivery zone, so the address-quality team wants every bad saved address listed for a fix-it prompt in the app.

A pincode is valid when it is exactly six digits and does not start with 0 — no spaces, no letters. Return address_id, customer_id and pincode for every saved address whose pincode is not valid, including addresses with no pincode at all (NULL). Order the rows by address_id.

A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.

Tables

Table: SavedAddress

ColumnTypeValues
address_idint
customer_idint
labelenumHome, Work, Other
pincodevarchar

Primary key: address_id.

One row per address a customer saved. pincode is stored exactly as typed, or NULL when it was left empty.

Example

The rows must come back in the order shown.

SavedAddress

address_idcustomer_idlabelpincode
1101Home560034
2101Work56003
3102Home060034
4103OtherNULL
5104Home400 076
6105Work5600341
7106Home110O17
8107Home500081

Output

address_idcustomer_idpincode
210156003
3102060034
4103NULL
5104400 076
61055600341
7106110O17
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Swiggy: Saved Addresses Without a Valid Pincode

Orders sent to an address with a broken pincode get routed to the wrong delivery zone, so the address-quality team wants every bad saved address listed for…

  • Difficulty: Easy
  • Topics: Strings
  • Dialect: MySQL
  • Problem: #951

Problem statement

Orders sent to an address with a broken pincode get routed to the wrong delivery zone, so the address-quality team wants every bad saved address listed for a fix-it prompt in the app.

A pincode is valid when it is exactly six digits and does not start with 0 — no spaces, no letters. Return address_id, customer_id and pincode for every saved address whose pincode is not valid, including addresses with no pincode at all (NULL). Order the rows by address_id.

A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.

Tables

Table: SavedAddress

ColumnType
address_idint
customer_idint
labelenum(Home, Work, Other)
pincodevarchar

Primary key: address_id.

One row per address a customer saved. pincode is stored exactly as typed, or NULL when it was left empty.

Examples

Example 1

SavedAddress

address_idcustomer_idlabelpincode
1101Home560034
2101Work56003
3102Home060034
4103OtherNULL
5104Home400 076
6105Work5600341
7106Home110O17
8107Home500081

Output

address_idcustomer_idpincode
210156003
3102060034
4103NULL
5104400 076
61055600341
7106110O17

How to solve Swiggy: Saved Addresses Without a Valid Pincode

It is easier to describe a good pincode than every way of breaking one, so write the valid shape as a regular expression and negate it. ^[1-9][0-9]{5}$ reads: the string starts, one digit from 1 to 9, exactly five more digits, the string ends. The anchors matter — without them 5600341 would contain a six-digit match and pass. A space (400 076), a letter O typed for a zero, a leading zero or a short code all fail the pattern.

The NULL rows need their own test. pincode NOT REGEXP … on NULL yields NULL, and WHERE keeps only true, so without pincode IS NULL the empty addresses would silently vanish from a report that is supposed to find them. The second alternative folds that into one expression with COALESCE(…, 0) = 0.

The first alternative spells the rule out without a full pattern: wrong length, a leading zero, or any non-digit character. It is longer but shows each failure mode separately. Every version is one scan of the table.

Reference solution (MySQL)

SELECT address_id, customer_id, pincode
FROM SavedAddress
WHERE pincode IS NULL
   OR pincode NOT REGEXP '^[1-9][0-9]{5}$'
ORDER BY address_id

Another way

SELECT address_id, customer_id, pincode FROM SavedAddress WHERE pincode IS NULL OR CHAR_LENGTH(pincode) <> 6 OR LEFT(pincode, 1) = '0' OR pincode REGEXP '[^0-9]' ORDER BY address_id

Another way

SELECT address_id, customer_id, pincode FROM SavedAddress WHERE COALESCE(pincode REGEXP '^[1-9][0-9]{5}$', 0) = 0 ORDER BY address_id

← Swiggy: Genie Fare by Distance Slab · Swiggy: Swiggy One Plans With No Order Placed →