LIKE operator problem (or is it just me?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LIKE operator problem (or is it just me?)

Author

Topic

jansen
Starting Member

5 Posts

Posted - 2002-09-16 : 05:11:47

Hello all!

I have a problem with the result of the like operator!

Try out ...:

create table tblName
(
vchrName varchar(10) null
)

insert into tblName (vchrName) values ('ab')
insert into tblName (vchrName) values ('aab')
insert into tblName (vchrName) values ('aaab')

select * from tblName where vchrName like 'a%'
select * from tblName where vchrName like 'aa%'

The first select I would expect all rows but it only gives me [ab]??

Best regards
Jansen

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-09-16 : 06:31:43

I get the expected results... All three rows when using 'a%'. Strange!?

jansen
Starting Member

5 Posts

Posted - 2002-09-16 : 09:44:34

Hello Andraax

I think we are looking at a possible bug report....
My coallation on the vchrName column is: Danish_Norwegian, dictonary sort, Accent Sensitive.

When I change it, it also works?!? - What collation is your vchrName column??

By the way...I'm running SQL2K with SP2

/Jansen

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-09-19 : 06:56:09

Mine is Swedish_Finnish, dictionary, accent sensitive.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-02 : 00:55:11

Use the binary sort collation: (case sensitive - hence - lower)

select * from tblName where lower(vchrName) collate Danish_Norwegian_BIN
like 'a%'
select * from tblName where lower(vchrName) collate Danish_Norwegian_BIN
like 'aa%'

from BOL:

quote:
Sorts and compares data in Microsoft® SQL Server™ tables based on the bit patterns defined for each character. Binary sort order is case-sensitive, that is lowercase precedes uppercase, and accent-sensitive. This is the fastest sorting order.
If this option is not selected, SQL Server follows sorting and comparison rules as defined in dictionaries for the associated language or alphabet.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 12/02/2002 00:59:16

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-12-02 : 07:42:37

It's because the Danish_Norwegian collation is (still) considering aa to be a separate letter from a. Of course, this is ambiguous so it doesn't work properly. The strange thing is that aa <> å unless the comparison is accent-insensitive.


SELECT 1
WHERE
  'aa' COLLATE Danish_Norwegian_CI_AS =
  'å' COLLATE Danish_Norwegian_CI_AS
-- returns nothing

SELECT 1
WHERE
  'aa' COLLATE Danish_Norwegian_CI_AI =
  'å' COLLATE Danish_Norwegian_CI_AI
-- returns 1

SELECT 1
WHERE
  'aa' COLLATE Danish_Norwegian_CI_AI =
  'á' COLLATE Danish_Norwegian_CI_AI
-- returns nothing

SELECT 1
WHERE
  'å' COLLATE Danish_Norwegian_CI_AI =
  'á' COLLATE Danish_Norwegian_CI_AI
-- returns nothing

Edited by - Arnold Fribble on 12/02/2002 07:43:06

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources