Working with macrons in sql server 2005

Question

I have a table whose schema is

declare @tbl table(field varchar(20))
insert into  @tbl 
select 'Pāo' union all select 'Paorum' union all select 'Some Pao'  union all select 'Pao'

If I want to search the record only on the macron, I am unable to do so

select * from @tbl where field = 'Pāo'

Output:

field
Pao
Pao

If I use like operator e.g.(like '%Pāo%') I am getting all the records.

I am interested only in the macron field.

How to do it? I even tried with nvarchar insetead of varchar after googling but with no improvement.

Please help.

Thanks

Answer 1

You need to use unicode:

declare @tbl table(field nvarchar(20))
insert into  @tbl 
select N'Pāo' union all 
select N'Paorum' union all 
select N'Some Pao' union all 
select N'Pao'

select * from @tbl where field = N'Pāo'