Data warehouses routinely ingest valuable information embedded in free text, where inconsistent structure makes it difficult to query, connect, and analyze. Incoming values can also require quality checks before they reach curated tables.
Microsoft Fabric Data Warehouse and the SQL analytics endpoint now support scalar regular expression functions in preview, so you can extract useful values, validate formats, standardize text, and mask sensitive data directly in T-SQL instead of adding a separate processing step.
What’s new in preview
Regular expressions provide a concise way to recognize, extract, and transform complex text patterns that would otherwise require multiple string operations. The preview introduces the following scalar functions:
- REGEXP_LIKE: checks whether text matches a pattern.
- REGEXP_REPLACE: replaces matching text to clean, standardize, or mask values.
- REGEXP_SUBSTR: extracts a substring that matches a pattern.
- REGEXP_INSTR: returns the position of a matching substring.
- REGEXP_COUNT: counts pattern occurrences in a string.
Use these functions during refinement to turn identifiers embedded in free text into structured values and to flag malformed records before they reach downstream systems. The following scenarios demonstrate both uses.
Extract useful values from raw text
In this fictional example, inbound customer emails land in bronze.EmailMessages. The Sender column contains a value such as “Avery Smith”, and an order reference such as “ORD-10293” may appear in the Subject column. REGEXP_SUBSTR extracts the sender’s name, email address, company identifier, and order number as structured values that can be related to curated customer, company, and order data.
Figure: Extracting structured values from email message text.For “Avery Smith ”, the query returns “Avery Smith” as the Name, “[email protected]” as the Email, and “contoso” as the Company. It also extracts the order number from the Subject column.
Validate contact data before it reaches downstream systems
Contact data in the silver.DimCustomer table can contain missing characters, extra spaces, or email addresses that do not follow the expected format. REGEXP_LIKE identifies records that fail the format check, so you can review, correct, or quarantine them before the dimension feeds reports, campaigns, and downstream applications.
SELECT SourceCustomerId, EmailAddress
FROM silver.DimCustomer
WHERE NOT REGEXP_LIKE(EmailAddress,
'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');The query isolates email addresses that do not match the expected format so they can be reviewed, corrected, or quarantined before the customer dimension is released to downstream consumers.
Conclusion
Regular expression functions help turn hard-to-use text into structured, trustworthy warehouse data. You can extract sender, company, and order details from inbound emails and validate contact values during refinement, all with concise T-SQL close to the source. The result is information that is easier to connect, analyze, and trust.
Explore the preview and learn how to use regular expression functions in Fabric Data Warehouse in the documentation. Share your scenarios and feedback with the Microsoft Fabric community on Reddit.
Microsoft Employee