Answered by:
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 SThursday, August 4, 2011 3:56 PM
Answers
-
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.
- Marked as answer by Albert S Monday, August 15, 2011 6:22 PM
Monday, August 15, 2011 2:22 AM
All replies
-
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, RussiaThursday, August 4, 2011 4:37 PM -
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, RussiaThursday, August 4, 2011 5:00 PM -
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 SThursday, August 4, 2011 6:53 PM -
To use one of these, would it have to be a pass-through query?
Thanks!
Albert S.
Albert SThursday, August 4, 2011 6:54 PM -
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, RussiaThursday, August 4, 2011 6:55 PM -
To use one of these, would it have to be a pass-through query?
Thanks!
Albert S.
Albert S
Andrey V Artemyev | Saint-Petersburg, RussiaThursday, August 4, 2011 6:57 PM -
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 SThursday, August 4, 2011 7:03 PM -
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, RussiaThursday, August 4, 2011 7:11 PM -
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, RussiaThursday, August 4, 2011 7:15 PM -
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:
It should return all 4 values you described above.SELECT tblInventory.ID, tblInventory.Title FROM tblInventory WHERE (((StrConv([Title],128))=StrConv("Boheme.",128)));
Andrey V Artemyev | Saint-Petersburg, RussiaThursday, August 4, 2011 7:44 PM -
Yes, we want to enter a word with "e" and find all the words with "ë", "ê", "é", "è".
I hope that make sense.
Thanks!
Albert S.
Albert SThursday, August 4, 2011 8:01 PM -
This one didn't work. It only returns the straight "Boheme."
Thanks!
Albert SThursday, August 4, 2011 8:04 PM -
It's really strange, in my case it returns all the records. I have a test table:
UniTest
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
Query8 ID TxtFld 1 Boheme 2 Böheme 3 Bohemé 4 Bohème
Andrey V Artemyev | Saint-Petersburg, RussiaThursday, August 4, 2011 8:06 PM -
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 SThursday, August 4, 2011 8:12 PM -
Try to explicitly convert your value to lower-case before comparing:
WHERE (((StrConv(LCase([Title]),128))=StrConv("boheme.",128)));
Andrey V Artemyev | Saint-Petersburg, RussiaThursday, August 4, 2011 8:29 PM -
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 SThursday, August 4, 2011 9:09 PM -
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- Proposed as answer by Macy Dong Monday, August 15, 2011 2:22 AM
Thursday, August 4, 2011 9:17 PM -
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.
- Marked as answer by Albert S Monday, August 15, 2011 6:22 PM
Monday, August 15, 2011 2:22 AM -
Excellent!
I hadn't gotten it to work, but your query does the trick.
Thanks again!
Albert S.
Albert SMonday, August 15, 2011 6:21 PM -
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:
Title Baba Bulleh Shah. Bábá Mo Túndé. Baba Yaga. Thanks for any additional help.
Albert SFriday, October 7, 2011 9:43 PM -
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:
Title Baba Bulleh Shah. Bábá Mo Túndé. Baba Yaga. Thanks for any additional help.
Albert SFriday, October 7, 2011 9:44 PM -
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 SFriday, October 7, 2011 10:49 PM