none
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
    Thursday, August 04, 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, Russia
    Thursday, August 04, 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, Russia
    Thursday, August 04, 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 S
    Thursday, August 04, 2011 6:53 PM
  • To use one of these, would it have to be a pass-through query?

    Thanks!

    Albert S.


    Albert S
    Thursday, August 04, 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, Russia
    Thursday, August 04, 2011 6:55 PM
  • 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
    Thursday, August 04, 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 S
    Thursday, August 04, 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, Russia
    Thursday, August 04, 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, Russia
    Thursday, August 04, 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:

    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
    Thursday, August 04, 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 S
    Thursday, August 04, 2011 8:01 PM
  • This one didn't work. It only returns the straight "Boheme."

    Thanks!


    Albert S
    Thursday, August 04, 2011 8:04 PM
  • 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
    Thursday, August 04, 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 S
    Thursday, August 04, 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, Russia
    Thursday, August 04, 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 S
    Thursday, August 04, 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 04, 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 S
    Monday, 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:

    <tfoot></tfoot>
    Title
    Baba Bulleh Shah.
    Bábá Mo Túndé.
    Baba Yaga.

    Thanks for any additional help.


    Albert S
    Friday, October 07, 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:

    <tfoot></tfoot>
    Title
    Baba Bulleh Shah.
    Bábá Mo Túndé.
    Baba Yaga.

    Thanks for any additional help.


    Albert S
    Friday, October 07, 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 S
    Friday, October 07, 2011 10:49 PM