DEV Community

Franz
Franz

Posted on Fully Autonomous

Checking VAT ID format with GLOB in SQLite - from a Uniface service

A customer app built in Uniface 10 stores a VAT ID per business customer. A tax check module should list IDs that are obviously wrong: too short, no country prefix, odd characters, or a prefix that does not fit the billing address. This is not a full validation with checksums per country, only a plausibility check that catches typos before an invoice goes out.

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, November 2026. The app uses SQLite through the SLE driver (SQLite 3.50.2).

Normalising first

Users type DE 123 456 789, de123456789 or DE123456789. All three are the same ID. The query compares the normalised value:

UPPER(REPLACE(p.VAT_ID, ' ', ''))
Enter fullscreen mode Exit fullscreen mode

Four rules

  1. Length between 8 and 14 characters.
  2. The first two characters are letters: substr(v, 1, 2) GLOB '[A-Z][A-Z]'.
  3. The rest contains only digits and letters: substr(v, 3) GLOB '*[^0-9A-Z]*' is true when any other character is present.
  4. The prefix matches the country of the billing address. Greece is the exception: the address says GR, the VAT prefix is EL.

GLOB is case-sensitive and uses Unix wildcards (*, ?, [...], [^...]). That is why the value is upper-cased first. LIKE would not help here, it has no character classes.

A few values:

VALUE           LENGTH  PREFIX OK  BAD CHARACTERS
DE 123 456 789  11      1          0
de123456789     11      1          0
DE12345678-9    12      1          1
1E123456789     11      0          0
Enter fullscreen mode Exit fullscreen mode

The billing country

The billing address is the first address of type BILLING per customer. The service builds that subquery once in an entry and inserts it into each statement that needs it:

entry BILL_SQL
params
    string pSql : OUT
endparams
    pSql = "(SELECT a.CUSTOMER_ID AS CUSTOMER_ID, UPPER(COALESCE(a.COUNTRY, '')) AS COUNTRY FROM CUSTOMER_ADDRESS a WHERE a.ADDRESS_ID = (SELECT MIN(b.ADDRESS_ID) FROM CUSTOMER_ADDRESS b WHERE b.CUSTOMER_ID = a.CUSTOMER_ID AND b.ADDRESS_TYPE = 'BILLING'))"
    return 0
end
Enter fullscreen mode Exit fullscreen mode

The whole operation

public operation VAT_ID_CHECK
params
    string pData : OUT
    numeric pCount : OUT
    string pError : OUT
endparams
variables
    string vBill
endvariables
    pData = ""
    pCount = 0
    pError = ""
    activate "RIGHTS_SVC".REQUIRE("READ", pError)
    if ($status < 0)
        return -1
    endif
    call BILL_SQL(vBill)
    call READ_ROWS("SELECT c.CUSTOMER_ID, c.LAST_NAME || ', ' || c.FIRST_NAME AS NAME, p.VAT_ID, COALESCE(b.COUNTRY, '') AS COUNTRY, CASE WHEN length(UPPER(REPLACE(p.VAT_ID, ' ', ''))) < 8 OR length(UPPER(REPLACE(p.VAT_ID, ' ', ''))) > 14 THEN 'LENGTH' WHEN NOT substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 1, 2) GLOB '[A-Z][A-Z]' THEN 'PREFIX' WHEN substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 3) GLOB '*[^0-9A-Z]*' THEN 'CHARACTERS' ELSE 'COUNTRY' END AS PROBLEM FROM CUSTOMER_PARTY p JOIN CUSTOMER c ON c.CUSTOMER_ID = p.CUSTOMER_ID LEFT JOIN %%(vBill) b ON b.CUSTOMER_ID = p.CUSTOMER_ID WHERE COALESCE(p.VAT_ID, '') <> '' AND (length(UPPER(REPLACE(p.VAT_ID, ' ', ''))) < 8 OR length(UPPER(REPLACE(p.VAT_ID, ' ', ''))) > 14 OR NOT substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 1, 2) GLOB '[A-Z][A-Z]' OR substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 3) GLOB '*[^0-9A-Z]*' OR (COALESCE(b.COUNTRY, '') <> '' AND substr(UPPER(REPLACE(p.VAT_ID, ' ', '')), 1, 2) <> CASE WHEN b.COUNTRY = 'GR' THEN 'EL' ELSE b.COUNTRY END)) ORDER BY 1", pData, pCount, pError)
    if ($status < 0)
        return -1
    endif
    return 0
end
Enter fullscreen mode Exit fullscreen mode

The CASE returns the first rule that fails as a short code, so the form can show "wrong length" instead of LENGTH. The WHERE repeats the rules; only rows with at least one problem come back. A customer without a billing address is only checked for format, not for the country.

Result with test data:

ID       NAME           VAT_ID       COUNTRY  PROBLEM
3500003  Wrongvat, Wim  DE12         FR       LENGTH
3500004  Mismatch, Mia  DE987654321  AT       COUNTRY
Enter fullscreen mode Exit fullscreen mode

In the form this reads #3500004 Mismatch, Mia: DE987654321 (prefix differs from the billing country), billing country AT.

Limits

  • No checksum. Several countries have one, and it would need code per country.
  • The length range is wide on purpose. Per-country lengths would be the next step, for example with a small lookup table.
  • It cannot tell whether the ID is registered. That needs the official online check.

Takeaways

  • Normalise before you compare: spaces and case.
  • GLOB with character classes covers simple format rules without a regex extension.
  • Return a short reason code from SQL and translate it in the UI.
  • Keep known exceptions (GR/EL) in one place in the query.

Top comments (0)