none
Finding unicode characters in SQL Search RRS feed

  • 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 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, Russia
    Thursday, 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, Russia
    Thursday, 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 S
    Thursday, August 4, 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 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, Russia
    Thursday, 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
    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 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 S
    Thursday, 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, Russia
    Thursday, 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, Russia
    Thursday, 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:

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

    Thanks!


    Albert S
    Thursday, August 4, 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 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 S
    Thursday, 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, Russia
    Thursday, 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 S
    Thursday, 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 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 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:

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

    Thanks for any additional help.


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