B2B Invoices With a Malformed GSTIN

Easy
101.

B2B Invoices With a Malformed GSTIN

EasyStringsBasics

MySQL

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

ColumnType
invoice_novarchar
buyer_namevarchar
buyer_gstinvarchar
taxable_valueint

Primary key: invoice_no.

One row per invoice to a business buyer. buyer_gstin may be NULL when the buyer never entered it.

Example

The rows must come back in the order shown.

b2b_invoices

invoice_nobuyer_namebuyer_gstintaxable_value
INV-24-0101Sharma Traders27AAPFU0939F1ZV42000
INV-24-0102Nair Agencies29AAGCB1286Q1Z18500
INV-24-0103Rao ElectricalsNULL9600
INV-24-0104Gupta & Sons07AABCS1429B1AK27300
INV-24-0105Bose Pharma19AACCB2230M1ZQ55000
INV-24-0106Patel Kirana24AAKFP5214L1ZPX7400

Output

invoice_nobuyer_name
INV-24-0102Nair Agencies
INV-24-0103Rao Electricals
INV-24-0104Gupta & Sons
INV-24-0106Patel Kirana
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.

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

ColumnType
invoice_novarchar
buyer_namevarchar
buyer_gstinvarchar
taxable_valueint

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_nobuyer_namebuyer_gstintaxable_value
INV-24-0101Sharma Traders27AAPFU0939F1ZV42000
INV-24-0102Nair Agencies29AAGCB1286Q1Z18500
INV-24-0103Rao ElectricalsNULL9600
INV-24-0104Gupta & Sons07AABCS1429B1AK27300
INV-24-0105Bose Pharma19AACCB2230M1ZQ55000
INV-24-0106Patel Kirana24AAKFP5214L1ZPX7400

Output

invoice_nobuyer_name
INV-24-0102Nair Agencies
INV-24-0103Rao Electricals
INV-24-0104Gupta & Sons
INV-24-0106Patel 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_no

Another 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_no

Another 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 →