Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    1

    Unanswered: Like operator ignores latin small letter a with dot above [ȧ] in select query.

    Hi,

    below query returns result in both the cases

    Actual Data

    ncharTitle
    -----------
    JȧS

    when running query

    select * from sampletable where ncharTitle like (N'%JȧS%')

    select * from sampletable where ncharTitle like (N'%JS%')

    returns same result ignoring 'ȧ' in 2nd query.

    My collation is Latin1_General_CI_AI

    Any help will be highly appreciated.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I get the same results on SQL Server 2008 R2 and 2012: the record is always returned.
    And CHARINDEX doesn't find the string at all.

    This looks like a bug to me.

    Code:
    CREATE TABLE DaTable (
    	ncharTitle	NCHAR(10)	COLLATE Latin1_General_CI_AI
    )
    
    INSERT INTO DaTable(ncharTitle) VALUES (N'JȧS')
    
    SELECT * FROM DaTable
    
    select * from DaTable where ncharTitle like (N'%JȧS%')
    select * from DaTable where ncharTitle like (N'%JȧS%') COLLATE Latin1_General_CI_AI
    select * from DaTable where ncharTitle like (N'%JȧS%') COLLATE Latin1_General_CI_AS
    
    select * from DaTable where ncharTitle like (N'%JS%')
    select * from DaTable where ncharTitle like (N'%JS%') COLLATE Latin1_General_CI_AI
    select * from DaTable where ncharTitle like (N'%JS%') COLLATE Latin1_General_CI_AS
    
    select CHARINDEX (N'%JȧS%', ncharTitle) from DaTable 
    --returns 0
    select CHARINDEX ((N'%JS%'), ncharTitle) from DaTable 
    --returns 0
    
    DROP TABLE DaTable;
    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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does collate apply to unicode (nchar) fields?
    George
    Home | Blog

Posting Permissions

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