'Azure SQL Database: Full text index on certain emails does not work
I have a full text index defined on user table's username column. When there is any character following + symbol in email in the search query there is no result. + is a valid symbol in email. Appreciate if you can explain why I see this behavior and any workaround for this.
/* returns result */
select * from AspNetUsers where contains(username, '"[email protected]*"')
select * from AspNetUsers where contains(username, '"foo+*"')
/* does not return result */
select * from AspNetUsers where contains(username, '"foo+77*"')
select * from AspNetUsers where contains(username, '"foo+7*"')
select * from AspNetUsers where contains(username, '"foo+77@gmail*"')
Solution 1:[1]
Adding backslash before character 7 returns results.
Before adding backslash \ in the query:
After adding backslash \ in the query:
QUERY:
select * from AspNetUsers where contains(username, '"foo+\77*"')
select * from AspNetUsers where contains(username, '"foo+\7*"')
select * from AspNetUsers where contains(username, '"foo+\77@gmail*"')
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | NiharikaMoola-MT |