February 7, 2008 at 7:25 am
Example:
-- drop table tbl_test
create table tbl_test
(
order_name char(30) not null,
constraint PK_tbl_test primary key(order_name)
)
go
insert tbl_test values('abcdefg')
insert tbl_test values('new customer')
insert tbl_test values('new-customer')
insert tbl_test values('new+customer')
insert tbl_test values('new.customer')
insert tbl_test values('new@customer')
insert tbl_test values('new,customer')
insert tbl_test values('zzzzzzz')
This query drives me crazy:
select *
from tbl_test
where order_name >= 'new'
order by order_name
Where is record 'new-custumer'? Does this mean that '-' is before nothing?
This query return ok!:
select *
from tbl_test
where order_name >= 'new-'
order by order_name
February 7, 2008 at 7:34 am
looks to me that it's further down on the list? as in - row # 4 of your SELECT statement?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 7:59 am
There is s crucial difference between selects:
first select
...
where order_name >= 'new' -- !! without "-"
second select
...
where order_name >= 'new-' -- !! with "-"
It works fine with this little modification:
...
where order_name >= N'new' -- with "N" for nchar
February 7, 2008 at 8:39 am
it works fine in either case...it just reorders your rows from how you inserted them...
No - reallly:)
I'm not sure what you're seeing, but here's what I get:
new customer
new+customer
new,customer
new-customer
new.customer
new@customer
zzzzzzz
I'm highlighting the row you're looking for.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 8:47 am
I get the same results as Matt.
Also tried it without the Order By, and it still kept the result you're looking for in the 4th row.
- 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
February 11, 2008 at 12:50 am
Strange behavior is caused by different database collation.
I run a test above using following collation: SQL_Slovenian_CP1250_CI_AS and "new-customer" record was missing.
It works fine using Latin1_General_CI_AS collation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply