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 Numbers INPUT ID Description 1 Widget A SN1234-5678 model 2 Gadget B no serial info available 3 Device C with SN9999-0001 code 4 Tool D has SN123-456 (invalid) Serial Pattern: SN + 4 digits + - + 4 digits SN####-#### e.g., SN1234-5678 ALGORITHM STEPS 1 Build Regex Pattern SN[0-9]{4}-[0-9]{4} 2 Scan Each Product Loop through all rows 3 Match Description Apply regex to text 4 Filter Valid Matches Return matching products Pattern Matching: SN1234-5678 OK no serial NO SN9999-0001 OK SN123-456 NO FINAL RESULT Valid Products Product 1 Widget A SN1234-5678 Product 3 Device C SN9999-0001 Total Found: 2 Products SQL Query: SELECT * FROM products WHERE description REGEXP 'SN[0-9]{4}-[0-9]{4}' Key Insight: Regular expressions provide powerful pattern matching for validating serial number formats. The pattern SN[0-9]{4}-[0-9]{4} ensures exactly: 'SN' prefix + 4 digits + hyphen + 4 digits. This approach filters invalid formats like SN123-456 (too few digits) or missing prefixes. TutorialsPoint - Find Products with Valid Serial Numbers | Optimal Solution

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