how to work with macrons and ms sql 2000/2005

[prabirchoudhury]

Hello

I am working on macrons name fields in MSSQL 2000/2005 database and it is not working for the select where fiels is macron. sql could not understand the search the index for the macron field.

any one have any idea how to keep( different format) the macron record and search for that records ..
exam ple code below..

select * from student where first_name like '%Māori%'”

thanks

[ck9663]

What error are you getting? Your code should return all records with first name with that string inside it.

~~ CK

[prabirchoudhury]

Hey CK
thanks for your reply. I am not getting any error but no match return but the matches are in the searching field. testing query is not making any error eather. any idea?

thanks again

[prabirchoudhury]

hello

INSERT INTO dbo.access (student_id,act ion, table_ref)
VALUES (100,'c', 'Māori')

here i cant insert "Māori" a macron on field into a table the datatype of the field is nvarchar(50)

any idea please, thanks

[prabirchoudhury]

how to work with macrons and ms sql 2000/200

Hello

1. I cant insert a macron on field 'Māori' it is not making any error but just taking the data without macron.
2. even a macron on field is present in the table bit the select query not getting any error but no match return but the matches are in the searching field.

INSERT INTO student(student_id,action, table_ref)
VALUES (100,'c', 'Māori') 
select * from student where first_name like '%Māori%'

many thanks

Prabir

[Frinavale]

Try changing the first_name field so that it's a nvarchar type instead of a varchar type.

Apparently NVARCHAR is supports two-byte characters which is needed for non-English characters.

Check out this MSDN article on International Features in MS SQL Server 2005

**Edit**

I just re-read your post and apparently you are using nvarchar.
I'm not sure what's wrong....sorry

-Frinny

[prabirchoudhury]

hey Frinny thanks for your reply..

[nbiswas]

Solution to how to work with macrons and ms sql 2000/2005

Use unicode

e.g.

declare @tbl table(field nvarchar(20))
insert into  @tbl 
select N'Māori' union all 
select N'Maori' union all 
select N'Some Māori' union all 
select N'Some Maori'

Solution

select * from @tbl where field like N'%Māori%'

Output

field
Māori
Some Māori

If you need an exact match

try :

select * from @tbl where field = N'Māori'

Output

field
Māori

Hope this helps