SQL Server 2025: JSON, regex and new functions

A partner sends 8 customer rows, and only 4 of them can be loaded: mobile numbers arrive as +91-98470-11111 or 98450 12345, one vehicle number is too short, and one preferences value is a bare JSON string. SQL Server 2025 can check all of it in T-SQL, with a json type that refuses what is not JSON and functions for regular expressionsregular expression: A pattern that describes a set of strings, such as ten digits that start with 6 to 9. SQL Server 2025's REGEXP functions test, extract, count and replace matches of patterns written in RE2 syntax..

You will store and read JSON in the new type, see how rows become JSON, clean and test the import’s text with the REGEXP functions, and check which of these features depend on the database’s compatibility level.

SQL Server 2025 stores JSON in a type that refuses what is not JSON

Until SQL Server 2025, JSON lived in nvarchar(max) columns, and nothing stopped a broken document unless a CHECK with ISJSON was added. The new json data typejson data type: The SQL Server 2025 type for JSON documents. It stores a parsed binary form, accepts only a JSON object or array at the top level and refuses other text with error 13609. stores documents in a binary format and accepts only a JSON object or array at the top level. Microsoft’s page names the refusals: a JSON scalar such as "email", or an empty string, fails with error 13609, and so does text that is not JSON at all. A duplicate key is not an error: the type keeps the first value. The type works at every compatibility level.

Six functions read JSON or build it:

The REGEXP functions clean and check text inside the database

SQL Server 2025’s regular expressions follow RE2, Google’s library, and come in two kinds. REGEXP_LIKE returns true or false, so it goes where T-SQL expects a condition: WHERE, CASE WHEN or a CHECK constraint. REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_COUNT return a value, and REGEXP_MATCHES and REGEXP_SPLIT_TO_TABLE return rows. Step through one row of the import:

The model applies the same three teaching shapes to all 8 rows, with the json type’s rules for the preferences. Its patterns use only what RE2 and Python read alike; press Edit and add a row of your own:

"""Clean and check a partner's import of 8 customer rows, the way the lesson's T-SQL does it.

Each mobile number keeps only its digits and may start with 91 or 0; what is left must be 10 digits that start with
6, 7, 8 or 9. Vehicle numbers lose their spaces and hyphens and must have the shape of two letters, one or two
digits, up to three letters and four digits. PIN codes keep their digits and must be six digits that do not start
with 0. These are teaching shapes, not official validation rules. The preferences must be JSON that SQL Server 2025's
json type accepts: an object or an array at the top level; a duplicate key keeps its first value.

The patterns use only what RE2, the library behind SQL Server's REGEXP functions, and Python's re both read the same
way: anchors, character classes, groups and counted repeats. Press Edit and add a row of your own to ROWS.
"""

import json
import re

MOBILE = r"^(91|0)?([6-9][0-9]{9})$"
VEHICLE = r"^[A-Z]{2}[0-9]{1,2}[A-Z]{0,3}[0-9]{4}$"
PIN = r"^[1-9][0-9]{5}$"

ROWS = [  # (row, mobile as sent, vehicle as sent, PIN as sent, preferences as sent)
    (1, "98450 12345", "KA 01 AB 1234", "560001", '{"lang":"kn","sms":true}'),
    (2, "+91-98470-11111", "KL07CD4321", "682001", '{"lang":"ml","sms":false}'),
    (3, "0522 2611234", "UP32 EF 0001", "226001", '{"lang":"hi"}'),
    (4, "9840012345", "TN 09 GH 5678", "600001", '{"lang":"ta","sms":true,"sms":false}'),
    (5, "98300-55555", "WB-02-AK-7777", "700001", '"email"'),
    (6, "+91 99999 00000", "DL 3C AB 1234", "110001", '{"lang":"en","sms":true}'),
    (7, "12345 67890", "MH 12 DE 1433", "411001", "[]"),
    (8, "8888 777 666", "GJ 1 KL 99", "380 001", "{}"),
]


def first_key_wins(pairs):
    out = {}
    for key, value in pairs:
        out.setdefault(key, value)
    return out


def refuse_constant(name):
    raise ValueError(f"{name} is not JSON")


def as_json(text):
    """The json type's rules: an object or an array at the top level, duplicate keys keep the first value."""
    try:
        value = json.loads(text, object_pairs_hook=first_key_wins, parse_constant=refuse_constant)
    except ValueError:
        return None, "not valid JSON"
    if not isinstance(value, (dict, list)):
        return None, "a JSON scalar, not an object or an array"
    return json.dumps(value, separators=(",", ":")), None


loaded, rejected, changed = 0, 0, 0
print(f"{'row':<4}{'mobile':<12}{'vehicle':<12}{'PIN':<8}{'preferences':<27}result")
for row, mobile_raw, vehicle_raw, pin_raw, prefs_raw in ROWS:
    reasons = []
    digits = re.sub(r"[^0-9]", "", mobile_raw)
    m = re.search(MOBILE, digits)
    mobile = m.group(2) if m else None
    if mobile is None:
        reasons.append("mobile")
    vehicle = re.sub(r"[ -]", "", vehicle_raw).upper()
    if not re.search(VEHICLE, vehicle):
        reasons.append("vehicle")
    pin = re.sub(r"[^0-9]", "", pin_raw)
    if not re.search(PIN, pin):
        reasons.append("PIN")
    prefs, why = as_json(prefs_raw)
    if prefs is None:
        reasons.append(f"preferences ({why})")
    changed += (mobile not in (None, mobile_raw)) + (vehicle != vehicle_raw) + (pin != pin_raw)
    changed += prefs is not None and prefs != prefs_raw
    result = "load" if not reasons else "reject: " + ", ".join(reasons)
    loaded += not reasons
    rejected += bool(reasons)
    print(f"{row:<4}{mobile or '-':<12}{vehicle:<12}{pin:<8}{(prefs or '-'):<27}{result}")
print(f"{loaded} rows to load, {rejected} to reject; cleaning changed {changed} values of the rows")

row mobile      vehicle     PIN     preferences                result
1   9845012345  KA01AB1234  560001  {"lang":"kn","sms":true}   load
2   9847011111  KL07CD4321  682001  {"lang":"ml","sms":false}  load
3   -           UP32EF0001  226001  {"lang":"hi"}              reject: mobile
4   9840012345  TN09GH5678  600001  {"lang":"ta","sms":true}   load
5   9830055555  WB02AK7777  700001  -                          reject: preferences (a JSON scalar, not an object or an array)
6   9999900000  DL3CAB1234  110001  {"lang":"en","sms":true}   load
7   -           MH12DE1433  411001  []                         reject: mobile
8   8888777666  GJ1KL99     380001  {}                         reject: vehicle
4 rows to load, 4 to reject; cleaning changed 14 values of the rows

Cleaning changed 14 values without a guess. Rows 3 and 7 are not mobile numbers once their digits are read, row 8’s vehicle number has two digits where four belong, and row 5’s preferences are a JSON string, not an object. Row 4 sends sms twice and keeps the first value. A regex tester helps to try a pattern on more samples before it goes into a constraint.

Most new features work at any level, a few need level 170

The compatibility levelcompatibility level: A setting of each SQL Server database that decides which Transact-SQL behaviours and features it uses; new databases get 170 on SQL Server 2025, 160 on 2022 and 150 on 2019. More in “PL/SQL and T-SQL: what they are and where they run” decides some of what a 2025 server offers a database. Microsoft’s pages say which features need a level and which do not:

The other additions are small and useful: the double-pipe operator joins strings, PRODUCT multiplies a column’s values, CURRENT_DATE returns today’s date, UNISTR turns escapes such as \20B9 into characters, BASE64_ENCODE writes binary as text, and SUBSTRING may leave out its length. The two similarity functions measure how alike two strings are. One older function belongs beside them for Indian data: FORMAT with the culture en-IN writes numbers with Indian digit grouping.

JSON in depth: the type against nvarchar, reads, and JSON built from rows Pro: opens with a Pro, Premium or Ultimate pass

Regex and the new functions in practice, with the import cleaned in T-SQL Pro: opens with a Pro, Premium or Ultimate pass

Practise: clean, check and read the import

Each question shows a value from the import, a function or a level. Decide what SQL Server 2025 does with it:

Interview questions

Three questions follow, about a regex check that fails on one server, a choice of type for an events table and PIN codes taken from addresses typed as plain text; write your answer before you open each first step:

Key takeaways

• SQL Server 2025’s json type accepts only a JSON object or array, refuses everything else with error 13609 and keeps the first value of a duplicate key.
• REGEXP_LIKE is a condition, the other REGEXP functions return values or rows, and all of them follow RE2.
• Only REGEXP_LIKE and the two table-valued REGEXP functions need compatibility level 170; the json type works at every level.
• Clean an import with patterns and keep the rows that fail with their reasons, rather than guessing a value.