May 1, 2008 at 8:09 am
Hi,
I have a table with a VARCHAR(50) column. The collation for the column is SQL_Latin1_General_CP1250_CS_AS.
The column contains Polish surnames. I want to search for LIKE '%Ł%' which is 0xA3 in CP1250. However I return names containing Ł and names containing L (where ther is no Ł ).
I have tried this (even though the columns collation is SQL_Latin1_General_CP1250_CS_AS).
SELECT * FROM tblNames WHERE Name COLLATE SQL_Latin1_General_CP1250_CS_AS LIKE '%Ł%' COLLATE SQL_Latin1_General_CP1250_CS_AS
The Servers default collation is Latin1_General_CI_AS
Any ideas what I am doing wrong.
Thanks
Allen
May 1, 2008 at 8:14 am
Have you tried using the char value?
select *
from table
where column like '%' + char(0xA3) + '%'
I just tried this:
create table #Test (
ID int identity primary key,
Val char(1))
insert into #test (val)
select char(0xA3) union all
select 'L'
select *
from #test
where val like '%' + char(0xA3) + '%'
And it worked. Just gave me the first row, not the second one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2008 at 8:33 am
Thanks.
Are you getting an L or a £ ?
I modified your code to include collation and got 2 x L, I need to match L to L but it is matching to L & L.
In Latin1_General 0xA3 gives a £.
create table #Test (
ID int identity primary key,
Val char(1)COLLATE SQL_Latin1_General_CP1250_CS_AS)
insert into #test (val)
select char(0xA3) union all
select 'L'
select *
from #test
where val like '%' + char(0xA3) + '%'
May 1, 2008 at 10:04 am
I'm getting "£". Didn't check for that.
Tested:
create table #Test (
ID int identity primary key,
Val char(1) COLLATE SQL_Latin1_General_CP1250_CS_AS )
insert into #test (val)
select char(0xA3) union all
select 'L'
select *
from #test
where val like '%' + char(0xA3) + '%'
And it gives both rows.
Guess I can't help you on this one. Out of my depth, since I've not dealt with non-US letters.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2008 at 10:33 am
Hm.. don't have too much time now, but this seems to work - you have to prefix your search string with N. Here's sample code:
--default collation is SQL_Latin1_General_CP1_CI_AS
create table testtable
(
col varchar(15) collate SQL_Polish_Cp1250_CI_AS,
ncol nvarchar(15)-- collate SQL_Polish_Cp1250_CI_AS
)
go
insert testtable(col, ncol) values (N'Lodka', N'Lodka')
insert testtable(col, ncol) values (N'Lódka', N'Lódka') --lódka - a small boat
go
select * from testtable where col like 'L%'
select * from testtable where ncol like 'L%'
select * from testtable where col like N'L%'
select * from testtable where ncol like N'L%'
go
drop table testtable
go
HTH
Piotr
...and your only reply is slàinte mhath
May 1, 2008 at 10:37 am
Okay, I'm kicking myself now. Should have thought to test with nchar, instead of char.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 9:22 am
Thanks Piotor - saved my week.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply