1

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.

Kannagi Lee
  • 23
  • 6

1 Answers1

2

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 + '%'
sep7696
  • 494
  • 2
  • 16
  • For my education, how did you know that about Vietnamese collations? Did you have prior knowledge or is there something that's able to be inspected to see that? – Ben Thul Aug 14 '23 at 14:37
  • @sep7696 Thank you. I think I should accept the way that treating "g" and "ng" is different. In my case, I need search a dynamic word. So, I predict that there are more problem coming if I replace words. – Kannagi Lee Aug 15 '23 at 03:46
  • @BenThul To be honest, I had this problem before and I asked one of my Vietnamese friends – sep7696 Aug 15 '23 at 05:17