B2B Invoices With a Malformed GSTIN — SQL Strings Problem
Business buyers can claim input tax credit only if their invoice carries a valid GSTIN.
- Difficulty: Easy
- Topics: Strings, Basics
- Dialect: MySQL
- Problem: #101
Problem statement
Business buyers can claim input tax credit only if their invoice carries a valid GSTIN. Before filing the monthly GSTR-1, the finance team flags invoices whose GSTIN fails two basic checks: a GSTIN is exactly 15 characters long, and its 14th character is always Z. A missing GSTIN (NULL) is malformed too.
Return every malformed invoice with the columns invoice_no and buyer_name, ordered by invoice_no. All GSTINs are stored in upper case.
Tables
Table: b2b_invoices
| Column | Type |
|---|---|
| invoice_no | varchar |
| buyer_name | varchar |
| buyer_gstin | varchar |
| taxable_value | int |
Primary key: invoice_no.
One row per invoice to a business buyer. buyer_gstin may be NULL when the buyer never entered it.
Examples
Example 1
b2b_invoices
| invoice_no | buyer_name | buyer_gstin | taxable_value |
|---|---|---|---|
| INV-24-0101 | Sharma Traders | 27AAPFU0939F1ZV | 42000 |
| INV-24-0102 | Nair Agencies | 29AAGCB1286Q1Z | 18500 |
| INV-24-0103 | Rao Electricals | NULL | 9600 |
| INV-24-0104 | Gupta & Sons | 07AABCS1429B1AK | 27300 |
| INV-24-0105 | Bose Pharma | 19AACCB2230M1ZQ | 55000 |
| INV-24-0106 | Patel Kirana | 24AAKFP5214L1ZPX | 7400 |
Output
| invoice_no | buyer_name |
|---|---|
| INV-24-0102 | Nair Agencies |
| INV-24-0103 | Rao Electricals |
| INV-24-0104 | Gupta & Sons |
| INV-24-0106 | Patel Kirana |
How to solve B2B Invoices With a Malformed GSTIN
An invoice is malformed when any one check fails, so the conditions are joined with OR: the GSTIN is missing, its length is not 15, or its 14th character is not Z.
The NULL case needs its own test. CHAR_LENGTH(NULL) <> 15 is NULL, not true, and so is the substring comparison, so without buyer_gstin IS NULL an invoice with no GSTIN would silently pass. Writing the rule as the negation of "valid" works too, provided the NULL is turned into something definite first — COALESCE(LENGTH(...), 0) = 15 is false for a missing GSTIN, so NOT (...) is true.
The 14th character can be read with SUBSTRING, with RIGHT(LEFT(s, 14), 1), or matched with the pattern LIKE '_____________Z_' (thirteen underscores, Z, one underscore — which also enforces the length of 15). Because GSTINs are upper case, the comparison with 'Z' never depends on collation. This is a single scan of the invoices; real validation would also check the state code and the checksum digit.
Reference solution (MySQL)
SELECT invoice_no, buyer_name
FROM b2b_invoices
WHERE buyer_gstin IS NULL
OR CHAR_LENGTH(buyer_gstin) <> 15
OR SUBSTRING(buyer_gstin, 14, 1) <> 'Z'
ORDER BY invoice_noAnother way
SELECT invoice_no, buyer_name FROM b2b_invoices WHERE NOT (COALESCE(LENGTH(buyer_gstin), 0) = 15 AND buyer_gstin LIKE '_____________Z_') ORDER BY invoice_noAnother way
SELECT invoice_no, buyer_name FROM b2b_invoices WHERE invoice_no NOT IN (SELECT invoice_no FROM b2b_invoices WHERE CHAR_LENGTH(buyer_gstin) = 15 AND RIGHT(LEFT(buyer_gstin, 14), 1) = 'Z') ORDER BY invoice_no← Seller GMV From Delivered Items · Coupon Redemption Summary per Code →