With the preview of new approximate string-matching functions and modern string-processing capabilities in Fabric Data Warehouse, SQL developers can compare strings by using EDIT_DISTANCE, EDIT_DISTANCE_SIMILARITY, JARO_WINKLER_DISTANCE, and JARO_WINKLER_SIMILARITY. They can also use ||, ||=, and UNISTR for more expressive string composition and Unicode handling.
Approximate string matching for real-world data
In real-world data, names and locations are not always entered consistently. For example, the same value might appear as Anna Maria and Anna-Maria, Hong Kong, Hongkong, and Hong-Kong, Philip and Phillip, or Allan and Alan. These differences make it harder to search, group, and standardize data, which is why developers need built-in functions that can compare strings even when they are not exact matches.
Fabric Data Warehouse enables you to use the following functions for comparing string similarity:
- EDIT_DISTANCE and JARO_WINKLER_DISTANCE measure how different two strings are. Use these functions when you want to understand how far one value is from another and compare possible matches.
- EDIT_DISTANCE_SIMILARITY and JARO_WINKLER_SIMILARITY return similarity scores that can be used to filter, rank, and group likely matches by using a simple threshold.
For example, imagine that we have invoice data where city names are not always entered consistently. Some rows may contain misspelled or differently entered names of towns such as Hongkong or Hong-Kong, and we want to find the entries that likely refer to Hong Kong.
The following examples show how these functions can be used to analyze invoice data by identifying similar city values and finding invoices with misspelled city names.
Example 1: Use distance to count similar town names in invoices
This example uses edit distance to find invoices with city names that are like Hong Kong.
Figure: Using EDIT_DISTANCE to find invoices with city names that are likely Hong Kong.
This query returns the number of invoices for cities that match Hong Kong exactly, as well as invoices where Hong Kong is entered in a slightly different way, such as Hongkong or Hong-Kong. It does this by keeping only city names that are within a small edit-distance threshold, which helps surface likely variations of the same city.
Example 2: Use similarity to identify invoices with misspelled city names
This query focuses on order data and helps identify city values that are like Hong Kong. It is useful when you want to find related variants in transactional data, even when the city name is entered with small spelling differences.
Figure: Using a similarity function to find invoices with possibly misspelled city names.
This returns order rows whose city names are similar enough to Hong Kong based on the selected similarity threshold. In practice, it helps surface values such as Hongkong, Hong-Kong, or other close variants that may need to be reviewed or grouped together.
Modern string processing in Fabric Data Warehouse
Fabric Data Warehouse is also expanding core string processing capabilities with ||, ||=, and UNISTR. The || operator brings ANSI-style string concatenation to T-SQL, and ||= adds a concise way to append to an existing string variable. Together, these operators make every day SQL authoring easier to read, easier to write, and more portable across database platforms. The new UNISTR function adds flexible support for Unicode string literals by allowing developers to specify Unicode escape sequences directly in a string, which is especially useful when working with international text, symbols, or characters outside the default code page. UNISTR supports multiple Unicode values and escape sequences and is more flexible for complex Unicode strings than functions such as NCHAR.
Conclusion
These additions help customers solve real data-quality problems directly in SQL, including fuzzy matching, typo detection, data standardization, and near-duplicate analysis. They also make every day query authoring more intuitive by adding modern string operators that improve readability and portability. Together, these capabilities help developers write clearer T-SQL, reduce manual cleanup in Pipelines, and build richer search and matching experiences in Fabric Data Warehouse.
Next Steps
Try the examples in your Fabric Data Warehouse environment to see how approximate string matching can help standardize real-world data and how modern string operators can simplify day-to-day T-SQL authoring. We also encourage you to share feedback as you explore these preview features.
To learn more about these preview capabilities, review the Microsoft Learn documentation for each function and operator:
Microsoft Employee