MAX(varchar + char(10) + varchar) unexpected resul

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)

MAX(varchar + char(10) + varchar) unexpected resul

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-05-19 : 10:51:24

[code]
SELECT MAX(MyCol)
FROM
(
SELECT [MyCol] = 'AAAAAAAAAAA'
UNION ALL SELECT 'AAAAAAAAAAA' + CHAR(13) + 'BBBBBBBBBB'
) X
[/code]
gives me the first row, not the second.

Is this known behaviour?

BoL says:

quote:

For character columns, MAX finds the highest value in the collating sequence.

is it therefore space-padding the first one to make same length as second one, and CHAR(13) < Space ?? If so that's a bind! is there a workaround?

Not my day ... :-(

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-19 : 10:54:58

hmm... gets the second one for me
what collation do you have?

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-19 : 15:31:37

Right on Spirit, collation thing.

SELECT MAX(MyCol)
FROM
(
	          SELECT [MyCol] = 'AAAAAAAAAAA' COLLATE SQL_Latin1_General_CP1_CI_AS -- Finnish_Swedish_CI_AS
	UNION ALL SELECT 'AAAAAAAAAAA' + CHAR(13) + 'BBBBBBBBBB'
) X

SELECT 1 WHERE CHAR(13) < '' -- COLLATE Finnish_Swedish_CI_AS

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-20 : 05:04:37

a bit more


select 	asci, 
	'''' + CHAR(asci) + '''' COLLATE Latin1_General_CI_AS, -- my db collation
	'''' + CHAR(asci) + '''' COLLATE SQL_Latin1_General_CP1_CI_AS, 
	'''' + CHAR(asci) + '''' COLLATE Finnish_Swedish_CI_AS
from 
(
SELECT ascii(' ') as asci
) t

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-20 : 05:33:07

It seems that adding CHAR(13) makes the string minimum than using ' '

Select Mycol from(
SELECT [MyCol] = 'AAAAAAAAAAA'
UNION ALL SELECT 'AAAAAAAAAAA' +CHAR(13) + 'BBBBBBBBBB') X
order by Mycol Asc

Select Mycol from(
SELECT [MyCol] = 'AAAAAAAAAAA'
UNION ALL SELECT 'AAAAAAAAAAA' +' '+ 'BBBBBBBBBB') X
order by Mycol Asc

Madhivanan

Failing to plan is Planning to fail

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 05:51:08

I have split feelings about collations.
So far they have caused me more grief than happiness.

In this case, it's the + operator that works different in the different collations.

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-05-26 : 01:47:31

Thanks folk. I fixed it by appending CHAR(0) to each string being UNION'd - haven't checked all the COLLATIONS but CHAR(0) < CHAR(13) on the COLLATION that I'm using!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources