Find Products with Valid Serial Numbers - Problem

You are given a table products that contains information about various products including their unique ID, name, and description.

Task: Find all products whose description contains a valid serial number pattern.

Valid Serial Number Rules:

  • Must start with the letters SN (case-sensitive)
  • Followed by exactly 4 digits
  • Must have a hyphen (-) followed by exactly 4 digits
  • The serial number can appear anywhere within the description

Pattern Example: SN1234-5678

Return the result table ordered by product_id in ascending order.

Table Schema

products
Column Name Type Description
product_id PK int Unique identifier for each product
product_name varchar Name of the product
description varchar Product description that may contain serial numbers
Primary Key: product_id
Note: Each row represents a product with its unique ID, name, and description

Input & Output

Example 1 — Mixed Valid and Invalid Patterns
Input Table:
product_id product_name description
1 Gaming Laptop High-performance laptop with SN1234-5678 for gaming
2 Office Phone Business phone without serial number
3 Tablet Pro Professional tablet SN9876-5432 with stylus
4 Monitor Display with invalid serial sn1111-2222
5 Keyboard Mechanical keyboard SN123-45678 extended
Output:
product_id product_name description
1 Gaming Laptop High-performance laptop with SN1234-5678 for gaming
3 Tablet Pro Professional tablet SN9876-5432 with stylus
💡 Note:

Products 1 and 3 have valid serial numbers (SN1234-5678 and SN9876-5432). Product 2 has no serial number, product 4 uses lowercase 'sn', and product 5 has incorrect digit counts (3 digits before hyphen, 5 after).
Example 2 — Edge Cases with Invalid Formats
Input Table:
product_id product_name description
6 Router Network device SN12345-6789 with extra digit
7 Camera Digital camera with SN0000-0001 serial
8 Printer Laser printer SN1234_5678 with underscore
9 Speaker Bluetooth speaker model contains no valid serial
Output:
product_id product_name description
7 Camera Digital camera with SN0000-0001 serial
💡 Note:

Only product 7 has a valid serial number SN0000-0001. Product 6 has 5 digits before hyphen, product 8 uses underscore instead of hyphen, and product 9 has no valid pattern.
Example 3 — No Valid Serial Numbers
Input Table:
product_id product_name description
10 Headphones Wireless headphones with great sound quality
11 Mouse Optical mouse for precise control
Output:
product_id product_name description
💡 Note:

No products contain valid serial numbers matching the SN####-#### pattern, so the result is empty.

Constraints

  • 1 ≤ product_id ≤ 1000
  • product_name and description contain only printable ASCII characters
  • Serial number pattern is case-sensitive and must be exactly SN followed by 4 digits, hyphen, and 4 digits

Visualization

Tap to expand
Find Products with Valid Serial NumbersInput: All Productsproduct_idnamedescription1LaptopHas SN1234-56782PhoneNo serial number3TabletDevice SN9876-54324MonitorInvalid sn1111-2222SIMILAR TOPattern MatchOutput: Valid Serial Numbersproduct_idnamedescription1LaptopHas SN1234-56783TabletDevice SN9876-5432Valid Pattern: SN####-####✓ SN1234-5678 (Valid)✓ SN9876-5432 (Valid)✗ sn1111-2222 (Lowercase)
Understanding the Visualization
1
Input
Products table with descriptions
2
Pattern Match
SIMILAR TO operator finds valid serial numbers
3
Output
Products with valid serial numbers only
Key Takeaway
🎯 Key Insight: Use SIMILAR TO with regex patterns to validate complex string formats efficiently

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
24.5K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen