Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: Searching by ignoring special characters

    Hi,

    In our schools we have a number of East-European, Turkish, Scandinavian, ... students. Their names contain "special" characters, like , , , ... Our users want to be able to search for student names without having to enter those special characters. Most often they don't know the exact spelling of the names and they get "no match found" messages as a result.

    They want to have persons with the name sgr, Osgueld, ... in the result set after entering "osgu" in the search field.

    What is the best way to do this?
    I was thinking about using another collation near the LIKE, but I don't know if that would work and how it should be done. The Database collation is Latin1_General_CI_AS.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Bloody users and their requirements...

    Perhaps a customised Soundex function would help here? It might be a hassle to write, but it would then be easy to update in the event of new characters appearing. I have one in VBA that could be amended and translated in T-SQL, probably.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @c			NVARCHAR(99)
    
    SET @c = 'sgr'
    
    SELECT
       CASE WHEN @c LIKE N'Osg%'
          THEN 'Yes'
          ELSE 'No'
       END AS Naked
    ,  CASE WHEN @c COLLATE Latin1_General_CI_AI LIKE N'Osg%'
          THEN 'Yes'
          ELSE 'No'
       END AS Fancy
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Pat,

    Thanks a lot!

    Latin1_General_CI_AS
    - CI stands for Case Insensitive
    - AS for Accent Sensitive

    Latin1_General_CI_AI
    - AI stands for Accent Insensitive. For me an accent is something like or , I didn't realise it also includes , and the likes.
    Last edited by Wim; 12-14-11 at 16:30.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by weejas View Post
    Bloody users and their requirements...


    I could see their point in this case.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •