[SOLVED] COLLATE Vietnamese_CI_AI is not working correctly with LIKE '%value%'

COLLATE Vietnamese_CI_AI is not working correctly with LIKE '%value%'

I have data in Vietnamese with accents: Bảo Linh, Ngọc Mai

If I search with query:

select * from profile where fullname COLLATE Vietnamese_CI_AI like '%ao%'

It returns Bảo Linh

Problem is when I search with:

select * from profile where fullname COLLATE Vietnamese_CI_AI like '%goc%'

Its not returns Ngọc Mai (search with "ngoc" instead of "goc" still give me result Ngọc Mai)

I want to have result Ngọc Mai when I search "goc"

T try N before search value but not work Please help me. Thank you.

Solution

The issue here is that the Vietnamese collations treat 'g' and 'ng' as different letters. So searching for 'goc' does not match 'Ngọc' even with the right collation.

To get your desired behavior of matching 'Ngọc' when searching for 'goc', you need to normalize the search term before querying.

DECLARE @searchTerm varchar(50) = 'goc'

SET @searchTerm = REPLACE(@searchTerm, 'g', 'ng')

SELECT * 
FROM profile
WHERE fullname COLLATE Vietnamese_CI_AI LIKE '%' + @searchTerm + '%'