got a problem when compare two strings

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)

got a problem when compare two strings

Author

Topic

marine8086
Starting Member

2 Posts

Posted - 2007-09-03 : 04:46:19

Hi, I wrote the following 2 sentences that comapre two strings,but they doesn't give the same result.

the second sames wrong.

please tell me why. Thanks a lot

if ('b0'>'b-')
print 'true'

if ('b01'>'b-1')
print 'true'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 05:19:49

I think some conversion is going on

if 'b0' > 'b-'
	print 'true 1' -- Printed for me

if 'b01' > 'b-1'
	print 'true 2' -- NOT printed for me

if 'b01' > 'b+1'
	print 'true 3' -- Printed for me

E 12°55'05.25"
N 56°04'39.16"

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-03 : 05:45:47

So is this a bug?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 06:10:40

Seems like it is

if 'b0' > 'b-1'
	print 'true 1.0' -- NOT printed for me
if 'b1' > 'b-1'
	print 'true 2.0' -- NOT printed for me
if 'b1' > 'b-1'
	print 'true 2.1' -- NOT printed for me
if 'b1-' > 'b-1'
	print 'true 2.2' -- Printed for me
if 'b10' > 'b-1'
	print 'true 2.3' -- Printed for me
if 'b2' > 'b-1'
	print 'true 3.0' -- Printed for me

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 07:07:42

quote:

Originally posted by Peso

Seems like it is

if 'b0' > 'b-1'
	print 'true 1.0' -- NOT printed for me
if 'b1' > 'b-1'
	print 'true 2.0' -- NOT printed for me
if 'b1' > 'b-1'
	print 'true 2.1' -- NOT printed for me
if 'b1-' > 'b-1'
	print 'true 2.2' -- Printed for me
if 'b10' > 'b-1'
	print 'true 2.3' -- Printed for me
if 'b2' > 'b-1'
	print 'true 3.0' -- Printed for me

E 12°55'05.25"
N 56°04'39.16"

All are printed for me

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 07:09:42

Which collation do you use?
I am using FINNISH_SWEDISH_CI_AS

E 12°55'05.25"
N 56°04'39.16"

marine8086
Starting Member

2 Posts

Posted - 2007-09-03 : 22:06:00

I am using Chinese_PRC_CI_AS.
See this:

create table a1(
word varchar(20))

insert into a1 select 'b0'
union all select 'b-'
union all select 'b01'
union all select 'b-1'

select word as 'Chinese_PRC_BIN word' from a1 order by word collate Chinese_PRC_BIN
returns:
Chinese_PRC_BIN word
--------------------
b-
b-1
b0
b01

select word as 'Chinese_PRC_CI_AS word' from a1 order by word collate Chinese_PRC_CI_AS
returns:
Chinese_PRC_CI_AS word
----------------------
b-
b0
b01
b-1

Why 'Chinese_PRC_CI_AS' orders like this??

I also found this:

if ('b0'collate Chinese_PRC_BIN > 'b-'collate Chinese_PRC_BIN)
print 'true 1' -- printed for me

if ('b01' collate Chinese_PRC_BIN > 'b-1' collate Chinese_PRC_BIN)
print 'true 2' -- printed for me

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-03 : 23:10:31

Using JAPANESE_CI_AS, all were printed.
Using CHINESE_PRC__CI_AS, same result as Peso's were printed.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 02:46:29

If you try to convert the text compared, to UNICODE?

if N'b0' > 'b-'
print 'true'

if N'b01' > 'b-1'
print 'true'

what happens then?

E 12°55'05.25"
N 56°04'39.16"

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-04 : 03:02:45

Same result using UNICODE.
It is very strange, now using JAPANESE_CI_AS, result is same as Peso's.
A few hours ago, all were returned.
I restarted the service and the result is stil same as Peso's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources