SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How does MAX() work on strings?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
419 Posts

Posted - 10/04/2013 :  15:48:29  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
I have ended up writing a select statement using the MAX() function on both an int field and a varchar() field. In my sample data everything is working fine but really don't understand how the MAX() function works on strings. Looking at MSDN the only clue I find is "For character columns, MAX finds the highest value in the collating sequence." but that makes no sense to me. Can anyone explain in smaller words how the MAX() function works on strings.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 10/04/2013 :  15:55:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's basically alphabetical order (but including numbers and all character-type data), like how Windows Explorer sorts the files if you do it by name. But collation plays a part here in SQL Server-land. The default collaction is case-insensitive, but if you used a case-sensitive collation, then sorting will be based on that.

Case-insensitive:
aaa
Aab
aCb

Case-sensitive:
aaa
aCb
Aab


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3327 Posts

Posted - 10/04/2013 :  16:02:05  Show Profile  Reply with Quote
I am not trying to be a wise guy, but it really depends on the collation.

Let us assume your collation is SQL_Latin1_General_CP1_CI_AS. This is probably your collation if you accepted defaults and you are in the United States. It is case insensitive, accent sensitive. For simple English letters, puncutations, and numbers, you can look up the ASCII key codes after converting everything to upper case, and that will be the sort order.

Instead, if you had SQL_Latin1_General_CP1_CS_AS, then the sort order would be case sensitive as well. So 'a' would higher than 'Z', but lower than 'z'.

It gets more complicated from there (for languages with non-latin scripts etc.). The collation of a column is really the definition of how all those characters are ordered.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3327 Posts

Posted - 10/04/2013 :  16:03:42  Show Profile  Reply with Quote


Typed the reply and got distracted (with real work) before I clicked the post button.
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
419 Posts

Posted - 10/04/2013 :  16:18:04  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
I have no idea what the collation is but the server is here in the US. How does the checking deal with symbles and other non alphanumeric characters (i.e. spaces, | , ', etc.)?

Also how would it deal with compairing two strings of differnet length (i.e. String 1 Len is 10 and String 2 Len is 16)?

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Edited by - Eagle_f90 on 10/04/2013 16:46:43
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3327 Posts

Posted - 10/04/2013 :  17:44:52  Show Profile  Reply with Quote
You can find what your server collation by querying the server - see here: http://technet.microsoft.com/en-us/library/hh230914.aspx
Your server collation: SELECT SERVERPROPERTY ( 'Collation' )
Your database collation: SELECT DATABASEPROPERTYEX('YourDatabaseNameHere', 'Collation')

Assuming you know your collation, if you want to see how the characters will be sorted, do an experiment like below, where I am creating a sert of characters and sorting them using case sensitive and case insensitive collations.
CREATE TABLE #tmp(id INT, x CHAR(1));

;WITH cte(x) AS
(
	SELECT 1 UNION ALL SELECT x+1 FROM cte WHERE x < 127
)
INSERT INTO #tmp
SELECT x, CHAR(x) FROM cte OPTION (MAXRECURSION 0)

-- case sensitive collation
SELECT * FROM #tmp ORDER BY x COLLATE SQL_Latin1_General_CP1_CS_AS, id DESC 

-- case insensitive collation
SELECT * FROM #tmp ORDER BY x COLLATE SQL_Latin1_General_CP1_CI_AS, id DESC 

DROP TABLE #tmp;


If you compare two strings of different lengths (or same length), it does a character by character comparison starting at the first character (in right to left character sets). When it finds a character that is different, then whichever is greater of those two characters determines which string is greater. When one string is shorter, and if the characters are the same upto the end of the shorter string, then the longer string is greater. You can try this example:
IF ('abcd' > 'abc') SELECT 1;
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
419 Posts

Posted - 10/08/2013 :  15:36:59  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
quote:
Originally posted by James K

You can find what your server collation by querying the server - see here: http://technet.microsoft.com/en-us/library/hh230914.aspx
Your server collation: SELECT SERVERPROPERTY ( 'Collation' )
Your database collation: SELECT DATABASEPROPERTYEX('YourDatabaseNameHere', 'Collation')

Assuming you know your collation, if you want to see how the characters will be sorted, do an experiment like below, where I am creating a sert of characters and sorting them using case sensitive and case insensitive collations.
CREATE TABLE #tmp(id INT, x CHAR(1));

;WITH cte(x) AS
(
	SELECT 1 UNION ALL SELECT x+1 FROM cte WHERE x < 127
)
INSERT INTO #tmp
SELECT x, CHAR(x) FROM cte OPTION (MAXRECURSION 0)

-- case sensitive collation
SELECT * FROM #tmp ORDER BY x COLLATE SQL_Latin1_General_CP1_CS_AS, id DESC 

-- case insensitive collation
SELECT * FROM #tmp ORDER BY x COLLATE SQL_Latin1_General_CP1_CI_AS, id DESC 

DROP TABLE #tmp;


If you compare two strings of different lengths (or same length), it does a character by character comparison starting at the first character (in right to left character sets). When it finds a character that is different, then whichever is greater of those two characters determines which string is greater. When one string is shorter, and if the characters are the same upto the end of the shorter string, then the longer string is greater. You can try this example:
IF ('abcd' > 'abc') SELECT 1;




Thanks this will give me good points to run some tests in dev to make sure everything will work out the way I want.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000