Finding unicode characters in SQL Search

Question

Hello,

Many records we store have unicode characters (char 192-255) in a field named "Title". We use search form that builds a SQL statement with parameters placed in various search fields including the "Title" field. Is there a function or a way to find a word with the unicode character, but the normal character is in the search field? For example:

Find "Boheme" - Return records with "Bohème" or "Bohemé" or "Böheme", etc.

We are using Access2003, SQLServer2005.

Thanks for any help!

Albert S.

---

Albert S

Answer 1

Hi Albert,

if you want to use it in a native Access query, you can use StrComp() function with a vbBinaryCompare option. For example,

SELECT * FROM tClients
WHERE StrComp(tClients.ClientName, [Enter the Name], 0) = 0

If you you are using .adp or want to make a Pass-Through query, I think you should look for the similar native SQL Server function.  

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 2

For native T-SQL unicode comparison see this article http://msdn.microsoft.com/en-us/library/ms189863(SQL.90).aspx

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 3

Hello,

I tried this, but it didn't return any results.:

SELECT tblInventory.Title
FROM tblInventory
WHERE StrComp([tblInventory].[Title],"Boheme*",0)=0;

Albert S.

---

Albert S

Answer 4

To use one of these, would it have to be a pass-through query?

Thanks!

Albert S.

---

Albert S

Answer 5

Why did you add * sign to the end of the name? Do you want to combine this methode with Like statement? First of all, try to make a query w/o this sign. Does it work?

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 6

To use one of these, would it have to be a pass-through query?

Thanks!

Albert S.

---

Albert S

StrComp won't work in Pass-Through, because it is a VBA function. All the methods from the link are for Pass-Through, because it is a native T-SQL syntax. 

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 7

Sorry, I should have mention the LIKE part...

I tried this:

SELECT tblInventory.ID, tblInventory.Title
FROM tblInventory
WHERE StrComp([tblInventory].[Title],"Boheme.",0)=0;

And it returns all the records of "Boheme.", but it doesn't return "Bohème."

Thanks!

Albert S.

---

Albert S

Answer 8

Em, 

I think this is exactly what you wanted to archieve. :) If you want to retrieve only "Bohème.", then it should be

 

WHERE StrComp([tblInventory].[Title],"Bohème.",0)=0;

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 9

It seems to be some misunderstanding. Do you want to search for a word with a particular unicode character only, or do you want to search for all the words with e-based unicode characters entering just a simple "e" in the search textbox?

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 10

In the second case, you can use StrConv function to convert your unicode values and then compare it with the result of StrConv function applied to your searching criteria:

SELECT tblInventory.ID, tblInventory.Title
FROM tblInventory
WHERE (((StrConv([Title],128))=StrConv("Boheme.",128)));

It should return all 4 values you described above.

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 11

Yes, we want to enter a word with "e" and find all the words with "ë", "ê", "é", "è".

I hope that make sense.

Thanks!

Albert S.

---

Albert S

Answer 12

This one didn't work. It only returns the straight "Boheme."

Thanks!

---

Albert S

Answer 13

It's really strange, in my case it returns all the records. I have a test table:

UniTest

<tfoot></tfoot>

UniTest

ID	TxtFld
1	Boheme
2	Böheme
3	Bohemé
4	Bohème
5	test

Then I have a query:

SELECT UniTest.ID, UniTest.TxtFld
FROM UniTest
WHERE (((StrConv([txtfld],128))=StrConv("Boheme",128)));

And the result of this query is:

Query8

<tfoot></tfoot>

Query8

ID	TxtFld
1	Boheme
2	Böheme
3	Bohemé
4	Bohème

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 14

This WHERE clause does make it case sensitive:

WHERE (((StrConv([Title],128))=StrConv("boheme.",128)));

This doesn't return any results, but this does:

WHERE (((StrConv([Title],128))=StrConv("Boheme.",128)));

I guess the bigger question is if there is a way around the character sensitivity like there is with case-sensitivity?

Thanks!

Albert S.

---

Albert S

Answer 15

Try to explicitly convert your value to lower-case before comparing:

WHERE (((StrConv(LCase([Title]),128))=StrConv("boheme.",128)));

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 16

That is strange. I thought it might problems with ODBC, etc. so I set up a test table exactly like yours and it doesn't work. My query only returns the first one.

Thanks!

Albert S.

---

Albert S

Answer 17

Agreed. This query works fine for me, it returns 4 records:

SELECT UniTest.ID, UniTest.TxtFld
FROM UniTest
WHERE (((StrConv(LCase([txtfld]),128))=StrConv("boheme",128)));

But I use Win7, SQL Server 2008 R2 Express and Access 2010. TxtFld is nvarchar(50). 

---

Andrey V Artemyev | Saint-Petersburg, Russia

Answer 18

Hi Albert,

 

Thank you for posting in our forum.

 

What's the status on your side?

I think Andrey has provided us with the key workaround. As he said But I use Win7, SQL Server 2008 R2 Express and Access 2010.

 

You can modify the query a little. I tested it on Access 2003 and it worked well.

 

SELECT UniTest.TxtFld,UniTest.ID

FROM UniTest

WHERE (((StrConv(LCase([txtfld]),64)) like StrConv("boheme",64)));

 

I hope this will help you resolve the issue.

If the issue persists, please feel free to let me know.

 

Hi Andrey,

 

Thank you very much for your friendly assistance!

The workaround is great!

 

Best Regards,

---

Macy Dong [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 19

Excellent!

I hadn't gotten it to work, but your query does the trick.

Thanks again!

Albert S.

---

Albert S

Answer 20

Hello,

I need to revisit this because this query returns all Titles that start with "b":

SELECT tblInventory.Title, tblInventory.ID
FROM tblInventory
WHERE (((StrConv(LCase([Title]),64)) like StrConv("boheme",64)));

Sample records returned:

<tfoot></tfoot>

Title
Baba Bulleh Shah.
Bábá Mo Túndé.
Baba Yaga.

Thanks for any additional help.

---

Albert S

Answer 21

Hello,

I need to revisit this because this query returns all Titles that start with "b":

SELECT tblInventory.Title, tblInventory.ID
FROM tblInventory
WHERE (((StrConv(LCase([Title]),64)) like StrConv("boheme",64)));

Sample records returned:

<tfoot></tfoot>

Title
Baba Bulleh Shah.
Bábá Mo Túndé.
Baba Yaga.

Thanks for any additional help.

---

Albert S

Answer 22

Ok, got this to work in a Pass-Through query:

SELECT tblInventory.Title, tblInventory.ID
FROM tblInventory
WHERE Title Collate SQL_Latin1_General_CP1_CI_AI LIKE 'Boheme%';

I might be able to use this.

---

Albert S