CODEWITHSUNDEEP
sql-serverazure-sql-database
frosty
frosty

Reputation: 2842

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*"')

Upvotes: 0

Views: 131

Answers (1)

NiharikaMoola
NiharikaMoola

Reputation: 5074

Adding backslash before character 7 returns results.

Before adding backslash \ in the query:

enter image description here

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*"')

enter image description here

Upvotes: 1

Related Questions