How to do Wildcard search using Azure AI Search or SQL like operator type behavior using Azure AI Search

Question

How to do Wildcard search using Azure AI Search or SQL like operator type behavior using Azure AI Search

Aravind Nandakumar 0

The Scenario
I have a scenario where I need to search for e-mail id or url like sample.name@example.com or https://sample.website.com/en-us so basically for something that contains special characters like (.[dot] -[hyphen] @[at symbol] '[single-quotes] "[double-quotes] :[colon] ;[semi:colon] ([paranthesis] {[curly-braces] ) along with text.

Problem
We are not able to search like wildcard or we are not able to achieve the SQL like operator kind of behavior using the Azure AI Search.
For example,

For e-mail
sample.name@example.c
mple.name@example.com
name@example.
e.name@ex

I expect all the above search requests to give me documents whose email field value is sample.name@example.com.

For url
https://sample.website.com/en
https://sample.website.com/
ps://sample.website.com/e
://sample.website.c

I expect all the above search requests to give me documents whose website field value is https://sample.website.com/en-us.

Requirement

What are the ways to achieve this?
What settings do we need to create the index to achieve this result?
What is the proper way of querying for that index so that we can obtain the required results?
Are there any other constructive suggestions?

Sina Salam 22,031 Reputation points Volunteer Moderator

2024-03-30T14:38:39.5133333+00:00
Hello @Aravind Nandakumar ,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

The Problem

I understand that you are facing challenges to implement wildcard search or SQL like operator behavior in Azure AI Search for searching email IDs or URLs containing special characters. This involves configuring the index with appropriate analyzers and token filters, mapping fields correctly, constructing wildcard queries, and optimizing search performance.

Solution

To solve the problem of implementing wildcard search or SQL-like operator behavior in Azure AI Search for scenarios involving email IDs or URLs with special characters, such as dots, hyphens, at symbols, quotes, colons, semicolons, and parentheses, follow these steps:

Define your index schema in Azure Cognitive Search with appropriate field types for email IDs and URLs. Create a custom analyzer that can handle special characters effectively. You can use the Microsoft Language Analyzer with custom tokenizers and token filters. For example, you can use the EdgeNGram token filter to generate n-grams of the input tokens. This is an example of how you can define a custom analyzer in Azure Cognitive Search:
{ "name": "custom_analyzer", "tokenizer": "standard", "tokenFilters": [ "lowercase", "asciifolding", "edgeNGramFilter" ] }

You will need to map the fields containing email IDs and URLs in your index definition to use the custom analyzer created in the previous step. This ensures that the special characters are properly tokenized and indexed for efficient wildcard searches. So, ensure that your index definition reflects the correct mapping for email and URL fields. The below is an example code snippet:
{ "name": "email", "type": "Edm.String", "analyzer": "custom_analyzer" }, { "name": "url", "type": "Edm.String", "analyzer": "custom_analyzer" }

To construct wildcard search queries, you will need to use the appropriate syntax for Azure Cognitive Search. Use wildcard characters (* or ?) appended to the search term to match patterns in email IDs or URLs. Here are examples of how you can construct wildcard queries: email:sample.name@example.c* email:*name@example.com email:name@example.* email:e.name@ex* url:https://sample.website.com/en* url:https://*sample.website.com/en-us url:https://sample.website.com/* url:https://sample.*.com/en

NOTE: When querying the Azure Cognitive Search index, use the constructed wildcard queries with the appropriate syntax in your code. Ensure that your application sends the queries to the Azure Cognitive Search service and processes the results accordingly. Use the Azure Cognitive Search SDK or REST API to send queries to your index and retrieve matching documents.

Summary

By following the above steps and suggestions, you can effectively implement wildcard search functionality for email IDs and URLs in Azure Cognitive Search, allowing your application to perform efficient and accurate searches for documents containing these patterns.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please remember to "Accept Answer" if answer helped, so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam
Aravind Nandakumar 0 Reputation points

2024-03-31T08:55:49.8033333+00:00
Hello @Sina Salam

Thank you for responding :-)

I just tried like you have suggested and it seems that it is not working as per my expectations.

Requirement

My requirement is very straight forward and I can explain it here again.

If I have a field email with value as ******@example.com I should get results with inifx as well as suffix as well as prefix searches.

******@example.com also should give documents which contain ******@example.com as value in the email field.

mple.name@example.c also should give documents which contain ple.name@exampl as value in the email field.

ame@example. also should give documents which contain ******@example.com as value in the email field.

.name@example. also should give documents which contain ******@example.com as value in the email field.

@example.c also should give documents which contain ******@example.com as value in the email field.

.name@exam also should give documents which contain ******@example.com as value in the email field.

sample.name@exampl also should give documents which contain ******@example.com as value in the email field.

le.name@example.com also should give documents which contain ******@example.com as value in the email field.

Here are a few observations I made after implementing it as per your answer and I want to share them with you.

I could not get those documents which contained email field's value as this ******@example.com , when I searched with the following ways

"filter": "search.ismatch('******@example.co*', 'email', 'full', 'any')"

"filter": "search.ismatch('sample.name@examp*', 'email', 'simple', 'all')"

"filter": "search.ismatch('*ple.name@example.com', 'email', 'simple', 'all')"

"filter": "search.ismatch('*le.name@exampl*', 'email', 'simple', 'all')"

"filter": "search.ismatch('/.*le.name@exampl.*/', 'email', 'full', 'all')"

Not just these I also tried with other combinations while querying like with any/all simple/full but I was not able to get the expected results whatsoever.

I also when tried to create a index with your answer there was a error which said me to use the latest edgeNGramFilter token filter named edgeNGram_v2 and it also asked me to add this line "@odata.type": "#Microsoft.Azure.Search.CustomAnalyzer" while creating the index using the json.

Why is this not working?, Is it because we also have special characters in between the text? will it work only for fields which contain simple texts?

What else could we do to make it work?

Please help in how we can achieve this using the Azure AI Search?

Could you tell me what is the best way to create the index with proper settings if you know

Also can you please give some example queries on how to query, so that we should be able get results for all infix, suffix and prefix text searches.

If you want any other information regarding my requirement, then please feel free to ask, I would be happy to explain again.

Best Regards,

Aravind Nandakumar
Sina Salam 22,031 Reputation points Volunteer Moderator

2024-03-31T10:50:11.6333333+00:00
Hi

Thank you for your feedback and your comment.

First, to address your requirements for wildcard searches on email addresses in Azure Cognitive Search, let's provide example queries and discuss the best approach to create the index:

Example Queries for Wildcard Searches:

Exact Match: To retrieve documents containing the exact email address "sample.name@example.com":

"filter": "search.ismatch('\"sample.name@example.com\"', 'email', 'full')"

Prefix Match: To find documents with email addresses starting with "sample.name@":

"filter": "search.ismatch('\"sample.name@*\"', 'email', 'prefix')"

Suffix Match: For documents having email addresses ending with ".com":

"filter": "search.ismatch('\"*example.com\"', 'email', 'suffix')"

Infix Match: To search for documents containing "name" within the email address:

"filter": "search.ismatch('\"*name*\"', 'email', 'full')"

The error

The error suggesting the use of edgeNGram_v2 and the requirement for @odata.type": "#Microsoft.Azure.Search.CustomAnalyzer" in the JSON indicate that there might be updates or changes in Azure Cognitive Search. To resolve this, ensure you're using the latest version of the edgeNGramFilter and include the required metadata in your index definition JSON.

Therefore, Creating the Index with Proper Settings:

Define a custom analyzer specifically tailored for email addresses. This analyzer should tokenize email addresses effectively, considering special characters such as dots, hyphens, and at symbols.

Utilize the edgeNGramFilter token filter within the custom analyzer to generate n-grams for wildcard searches. Ensure to use the latest version, such as edgeNGram_v2, if prompted.

Map the "email" field in your index definition to use the custom analyzer created above. This ensures that email addresses are properly tokenized and indexed for wildcard searches.

Ensure to include the required metadata, such as @odata.type": "#Microsoft.Azure.Search.CustomAnalyzer", in your index definition JSON to avoid any errors.

Success
Aravind Nandakumar 0 Reputation points

2024-04-02T06:16:53.73+00:00
Hello
@Sina Salam

I'm sorry to say that even this did not work.

Observations

I have created the index and that is successful but when querying there are only 2 types of queryTypes, one is "queryType": "full" and another is "queryType": "simple", but the ones suggested by you (suffix, prefix) are not available and by using them I was not able to execute the query.

Also even when I used the filters you provided for exact and infix (only these 2 because suffix, prefix do not exist at all), they yielded unexpected wrong results and sometimes they did not give any match at all.

Can you please suggest any other way of doing this?

Note

We are using the Azure AI Search which was earlier called as the Azure Cognitive Search.

Best Regards,

Aravind Nandakumar
Sina Salam 22,031 Reputation points Volunteer Moderator

2024-04-02T09:05:20.71+00:00
Hi Aravind Nandakumar,

Thank you for your feedback.

With all the information it's glaring something is missing somewhere, I have answered a complex query of this type, if I found the link I will forward it to you.

Meanwhile, the following are technical way of solving this kind of scenarios:

Reevaluate your data modeling approach to optimize for wildcard searches on email addresses. Consider splitting email addresses into separate fields based on different parts (e.g., username, domain) and apply appropriate analyzers and tokenizers to each field. This finer granularity can improve the accuracy of wildcard searches and help avoid unexpected results.

Instead of relying solely on built-in filters and analyzers, you can create a custom tokenizer tailored specifically for email addresses. This tokenizer should effectively handle email address structures, including special characters like dots and at symbols. By implementing a custom tokenizer, you have more control over how email addresses are tokenized and indexed, potentially improving the accuracy of wildcard searches.

Prior to indexing, preprocess email addresses to extract meaningful components (e.g., username, domain) and store them in separate fields. This allows for more precise searching and filtering based on specific components of email addresses. You can then use standard query types and filters to search within these components, such as searching for usernames or domains.

If your wildcard search requirements are complex and cannot be fully addressed within Azure Cognitive Search, consider performing preprocessing and wildcard matching externally before querying the search index.

Goodluck

Your answer

Aravind Nandakumar 0 Reputation points

2024-04-02T06:16:53.73+00:00

Hello
@Sina Salam

I'm sorry to say that even this did not work.

Observations

I have created the index and that is successful but when querying there are only 2 types of queryTypes, one is "queryType": "full" and another is "queryType": "simple", but the ones suggested by you (suffix, prefix) are not available and by using them I was not able to execute the query.

Also even when I used the filters you provided for exact and infix (only these 2 because suffix, prefix do not exist at all), they yielded unexpected wrong results and sometimes they did not give any match at all.

Can you please suggest any other way of doing this?

Note

We are using the Azure AI Search which was earlier called as the Azure Cognitive Search.

Best Regards,

Aravind Nandakumar
Sina Salam 22,031 Reputation points Volunteer Moderator

2024-04-02T09:05:20.71+00:00

Hi Aravind Nandakumar,

Thank you for your feedback.

With all the information it's glaring something is missing somewhere, I have answered a complex query of this type, if I found the link I will forward it to you.

Meanwhile, the following are technical way of solving this kind of scenarios:

Reevaluate your data modeling approach to optimize for wildcard searches on email addresses. Consider splitting email addresses into separate fields based on different parts (e.g., username, domain) and apply appropriate analyzers and tokenizers to each field. This finer granularity can improve the accuracy of wildcard searches and help avoid unexpected results.

Instead of relying solely on built-in filters and analyzers, you can create a custom tokenizer tailored specifically for email addresses. This tokenizer should effectively handle email address structures, including special characters like dots and at symbols. By implementing a custom tokenizer, you have more control over how email addresses are tokenized and indexed, potentially improving the accuracy of wildcard searches.

Prior to indexing, preprocess email addresses to extract meaningful components (e.g., username, domain) and store them in separate fields. This allows for more precise searching and filtering based on specific components of email addresses. You can then use standard query types and filters to search within these components, such as searching for usernames or domains.

If your wildcard search requirements are complex and cannot be fully addressed within Azure Cognitive Search, consider performing preprocessing and wildcard matching externally before querying the search index.

Goodluck

Share via

How to do Wildcard search using Azure AI Search or SQL like operator type behavior using Azure AI Search

The Problem

Solution

Summary

Requirement

Example Queries for Wildcard Searches:

The error

Observations

Note

Your answer