Like search in SQL server 2008 x64 BUG?

Prev: Creating a view with concatenated columns
Next: Replication Script-Out 2008 Full?

From: KS on 22 Jan 2010 09:49

---

I am having problem with a like search in SQL server 2008.
It works in x86 but not in x64.
The server has collation Sami_Sweden_Finland_100_CI_AS

Steps to reproduce:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

CREATE TABLE dbo.forsakringsgivare2(
namn varchar(40) COLLATE Sami_Sweden_Finland_100_CI_AS NOT NULL
) GO
CREATE TABLE dbo.forsakringsgivare3(
namn varchar(40) COLLATE Finnish_Swedish_CI_AI NOT NULL
)GO

insert into forsakringsgivare2 values('L�nsf�rs�kringar')
insert into forsakringsgivare3 values('L�nsf�rs�kringar')

--this select returns no data
SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'L�ns%'

--all the following returns data correctly
SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like '%L�ns%'
SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'L�nsf%'
SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn COLLATE
Finnish_Swedish_CI_AI like 'L�ns%'
SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn COLLATE
Latin1_General_CI_AS like 'L�ns%'

SELECT namn,* FROM dbo.forsakringsgivare3 WHERE namn like 'L�ns%'
SELECT namn,* FROM dbo.forsakringsgivare3 WHERE namn COLLATE
Finnish_Swedish_CI_AI like 'L�ns%'
SELECT namn,* FROM dbo.forsakringsgivare3 WHERE namn COLLATE
Latin1_General_CI_AS like 'L�ns%'

Anybody any clue?

/k

From: Stefan Hoffmann on 22 Jan 2010 10:00

---

hi K,

On 22.01.2010 15:49, KS wrote:
> I am having problem with a like search in SQL server 2008.
> It works in x86 but not in x64.
> The server has collation Sami_Sweden_Finland_100_CI_AS
>
> --this select returns no data
> SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'L�ns%'
While I can't explain it, simply marking your literal as unicode works:

SELECT * FROM dbo.forsakringsgivare2 WHERE namn like N'L�ns%';


mfG
--> stefan <--

From: KS on 22 Jan 2010 10:02

---

I have investigated further.

It seems that if the search string does not start with a %
and ends with s% it fails.

Try inserting Opossum
and run

SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'Opo%'
SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'Opos%'
SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'Oposs%'
SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'Opossu%'

/k

From: Stefan Hoffmann on 22 Jan 2010 11:23

---

hi,

On 22.01.2010 16:00, Stefan Hoffmann wrote:
>> I am having problem with a like search in SQL server 2008.
>> It works in x86 but not in x64.
>> The server has collation Sami_Sweden_Finland_100_CI_AS
>>
>> --this select returns no data
>> SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'L�ns%'
> While I can't explain it, simply marking your literal as unicode works:
>
> SELECT * FROM dbo.forsakringsgivare2 WHERE namn like N'L�ns%';
Tested on

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009
10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Developer
Edition (64-bit) on Windows NT 6.1 <X64> (Build 7100: )


mfG
--> stefan <--

From: Erland Sommarskog on 22 Jan 2010 18:24

---

KS (KurtSune(a)KurtSune.com) writes:
> I am having problem with a like search in SQL server 2008.
> It works in x86 but not in x64.
> The server has collation Sami_Sweden_Finland_100_CI_AS
>
> Steps to reproduce:
>
> SET ANSI_NULLS ON
> SET QUOTED_IDENTIFIER ON
> SET ANSI_PADDING ON
> GO
>
> CREATE TABLE dbo.forsakringsgivare2(
> namn varchar(40) COLLATE Sami_Sweden_Finland_100_CI_AS NOT NULL
> ) GO
> CREATE TABLE dbo.forsakringsgivare3(
> namn varchar(40) COLLATE Finnish_Swedish_CI_AI NOT NULL
> )GO
>
> insert into forsakringsgivare2 values('L�nsf�rs�kringar')
> insert into forsakringsgivare3 values('L�nsf�rs�kringar')
>
> --this select returns no data
> SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'L�ns%'

I believe this is the bug that SQL Server MVP Dan Guzman spotted.
See
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackI
D=484426

According to the resolution, the fix is available in CU4 of SQL 2008 SP1.
And indeed, I have this CU on a machine, and the issue does not appear
on this server.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: Creating a view with concatenated columns
Next: Replication Script-Out 2008 Full?