logo
  • Home
  • My Works
  • Downloads
  • Personal
  • Guest Book
  • About Me

Issue with Like operator in SQL Server with Norwegian collation

Published by Rafeeque B Muhammad on Saturday, January 23, 2010 12:00 AM
Updated on Saturday, January 23, 2010 12:00 AM


Look the given script what output you are expecting.

declare @myTable Table (fname nvarchar(100),lname nvarchar(100))

 

insert into @myTable values('test1','test2')

insert into @myTable values('aaa','test2')

insert into @myTable values('aa a','test2')

insert into @myTable values('a','test2')

insert into @myTable values('aa','test2')

insert into @myTable values('b','test2')

insert into @myTable values('vv','test2')

insert into @myTable values('aakash','test2')

insert into @myTable values('ab','test2')

insert into @myTable values('å','test2')

insert into @myTable values('ss','test2')

 

select * from @myTable where fname like '[a-z]%'

I was expecting all the records. But I couldn’t get all, after that I was searching something related to collation and found that client was created this db with collation Danish_Norwegian_CS_AS.

 

Then I tried to change the collation in sql itself and got the result what I was expecting.

select * from @myTable where fname like '[a-z]%' COLLATE SQL_Latin1_General_Cp437_BIN

 

The problem is facing only with aa, the reason for this is in Norwegian collation ‘aa’ will consider as a single character and will give the different result, while sorting also we will get something different output

Technical Articles

  • All
  • .NET Framework
  • ASP.NET
  • C#
  • Win Forms
  • WCF
  • SharePoint(WSS 3.0)
  • SQL Server
  • TSQL
  • Oracle
  • PL/SQL
  • Performance and Tuning
  • Application and Tools
  • Outlook programming
  • HTML/HTML5/XHTML/DHTML/JAVAScript/CSS
  • Windows Services
  • Web Services
  • Security
  • Web
  • General
  • Architecture

Technical Books

  • All
  • C# Threading
  • WCF

Other Links

  • Important Links

Techies Craft © 2014 | Privacy Policy