cant search in Cyrillic

[stanislavnba]

Hello, Ive just migrated my database to SQL.
And everything seems to work just fine, but I have this problem. I live in Bulgaria, and most of my DB data is in Cyrillic.
When I try to search by Customer Name in Access , I cant.
When I go to SSMS , and do a query

SELECT * FROM PopulationInfo WHERE [First Name] LIKE N'Мария' ORDER BY ClientCode

it works.
Problem is I cant enter this code in Access Query.
It says syntax error , missing operator.
Can anybody help?

 

[arnelgp]

you have single quote in your criteria.

try this:

SELECT * FROM PopulationInfo WHERE [First Name] LIKE "N'Мария" ORDER BY ClientCode

 

[stanislavnba]

you have single quote in your criteria.

try this:

SELECT * FROM PopulationInfo WHERE [First Name] LIKE "N'Мария" ORDER BY ClientCode

Dude did you not read my post?
I tried entering this in the Access - SQL View, but it gives me syntax error.
and Ideally I dont want to search just for Maria , but for whatever I type.

 

[arnelgp]

I read your post.
did you also enclosed it in double quote in access, because in your post it doesn't have.


UPDATE: I did add N'Мария' to my MSSQL table, I have a access db linked to the sql. I ran the query I gave you and bingo.

 

[MajP]

I use something like this in my Find as you type code

Private Function InternationalCharacters(ByVal strText As String) As String
   InternationalCharacters = strText
   'If you type international characters then turn them first to english
    'Type international and get english Add others as necessary á, é, í, ó, ú, ü, ñ
    'I do not know which ones are supported by keyboards but you may have to include
    'all seen below
    InternationalCharacters = Replace(InternationalCharacters, "á", "a")
    InternationalCharacters = Replace(InternationalCharacters, "é", "e")
    InternationalCharacters = Replace(InternationalCharacters, "í", "i")
    InternationalCharacters = Replace(InternationalCharacters, "ó", "o")
    InternationalCharacters = Replace(InternationalCharacters, "ú", "u")
    InternationalCharacters = Replace(InternationalCharacters, "ü", "u")
    InternationalCharacters = Replace(InternationalCharacters, "ñ", "n")
        

   'Type english and get international
    InternationalCharacters = Replace(InternationalCharacters, "a", "[aàáâãäå]")
    InternationalCharacters = Replace(InternationalCharacters, "e", "[eèéêë]")
    InternationalCharacters = Replace(InternationalCharacters, "i", "[iìíîï]")
    InternationalCharacters = Replace(InternationalCharacters, "o", "[oòóôõöø]")
    InternationalCharacters = Replace(InternationalCharacters, "u", "[uùúûü]")
    InternationalCharacters = Replace(InternationalCharacters, "n", "[nñ]")
    InternationalCharacters = Replace(InternationalCharacters, "y", "[yýÿ]")
    InternationalCharacters = Replace(InternationalCharacters, "z", "[zž]")
    InternationalCharacters = Replace(InternationalCharacters, "s", "[sš]")
    InternationalCharacters = Replace(InternationalCharacters, "d", "[dð]")
  
End Function

you may need to expand it for cryllic. But should be able to do something like
SELECT * FROM PopulationInfo WHERE [First Name] LIKE internationalCharacters("N'Мария") ORDER BY ClientCode

However need to also handle the ' with ''. So need to add the lines to the beginning of the function

  strText = Replace(strText, "'", "''")
  strText = Replace(strText, "#", "[#]")

 

[stanislavnba]

you have single quote in your criteria.

try this:

SELECT * FROM PopulationInfo WHERE [First Name] LIKE "N'Мария" ORDER BY ClientCode

Sorry about that .
I made an Access query with the statement you typed.
Now it runs like before. It shows no data.


@MajP I kind of get your code , but I need to do it for 30 letters and combinations and Im not sure I can figure it out , how to work after that

 

[arnelgp]

did you received any error.
on your query, you used it as if you are using equality operator (=), because there is no wild card character (*).

SELECT * FROM PopulationInfo WHERE [First Name] LIKE "*N'Мария*" ORDER BY ClientCode

 

[arnelgp]

I don't think translation is an issue here.
the text is Cyrillic, so if your search string is also Cyrillic it will find it if it is in there.
this goes also to Arabic text.

 

[stanislavnba]

I don't think translation is an issue here.
the text is Cyrillic, so if your search string is also Cyrillic it will find it if it is in there.
this goes also to Arabic text.

Here is how my query looks without any search criteria.

this is how it looks when I either enter a cyrillic name , or enter the statement you gave me.

this is what the desing of the query looks like with your statment

and this is what the usual design of my query looks like

Keep in mind that If I use Latin letters to enter a new customer, the query will work

I got no clue what to do

Its not letting me post images

 

[stanislavnba]

UPDATE: I did add N'Мария' to my MSSQL table, I have a access db linked to the sql. I ran the query I gave you and bingo.

I just saw this update.
Do I have to have N'xxxx' inside all the fields with cyrillyc in it ?
because I dont.
The data in the fields is just plain cyrrilic with no N before it .

 

[The_Doc_Man]

Let's look at the original query about which you complained.

SELECT * FROM PopulationInfo WHERE [First Name] LIKE N'Мария' ORDER BY ClientCode

You note that you get a "Missing operator" error. You state later that you wish to substitute something that you will type into that string so you can do the search. The error is caused by the letter "N" being followed by something in quotes. Normally, if you were trying to prefix a string with the letter "N" you would use an ampersand as the concatenation operator, as N & variable-holding-string

You have shown us your code, but we need to explicitly determine your intent. When you search, PROCEDURALLY what are you looking for? Because LIKE doesn't make sense in this context. You have no wild-card characters so LIKE and = will behave identically.

 

[stanislavnba]

Let's look at the original query about which you complained.

SELECT * FROM PopulationInfo WHERE [First Name] LIKE N'Мария' ORDER BY ClientCode

You note that you get a "Missing operator" error. You state later that you wish to substitute something that you will type into that string so you can do the search. The error is caused by the letter "N" being followed by something in quotes. Normally, if you were trying to prefix a string with the letter "N" you would use an ampersand as the concatenation operator, as N & variable-holding-string

You have shown us your code, but we need to explicitly determine your intent. When you search, PROCEDURALLY what are you looking for? Because LIKE doesn't make sense in this context. You have no wild-card characters so LIKE and = will behave identically.

Im just looking to see all the clients with specific name.
It used to work fine when it was only on the Access DB.
So I wonder if I didint prepare my DB properly for the migration.
Is there any setting that I should have used?

 

[stanislavnba]

I just need to see all the clients with a specific name.
It used to work just fine under the Access DB alone.
But after the migration it doesnt. Access doesnt seem to index or see the cyrillic properly?
Is there some setting that I shouldve done before the migration ?

 

[isladogs]

Three posts were moderated and have now been approved.
Posting this to trigger email notifications

 

[The_Doc_Man]

It used to work fine when it was only on the Access DB.

With the quoting syntax you showed us, that won't work correctly in Access SQL. If it did, you aren't telling us something because that syntax is WRONG.

 

[stanislavnba]

With the quoting syntax you showed us, that won't work correctly in Access SQL. If it did, you aren't telling us something because that syntax is WRONG.

Man come on. In Access DB the syntax is the normal
=[name]
And it works just fine.
So far I did a workaround where I have added a column for entering the name in Latin, but that's not very elegant.

 

[The_Doc_Man]

My comment was because of this syntax:

SELECT * FROM PopulationInfo WHERE [First Name] LIKE[COLOR="Red"] N'Мария'[/COLOR] ORDER BY ClientCode

The apostrophes in that context will not work without proper supporting syntax because the N is outside the quotes and has no operator between it and the quoted string. That is NOT "normal" syntax. If you had double quotes around the search-string, it would work differently, but the quoting as shown just doesn't work there.

 

[stanislavnba]

My comment was because of this syntax:

SELECT * FROM PopulationInfo WHERE [First Name] LIKE[COLOR="Red"] N'Мария'[/COLOR] ORDER BY ClientCode

The apostrophes in that context will not work without proper supporting syntax because the N is outside the quotes and has no operator between it and the quoted string. That is NOT "normal" syntax. If you had double quotes around the search-string, it would work differently, but the quoting as shown just doesn't work there.

Can you write down what should work ? So far nothing has worked.