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
 General SQL Server Forums
 New to SQL Server Programming
 How does MAX() work on strings?

Author  Topic 

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-10-04 : 15:48:29
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

38200 Posts

Posted - 2013-10-04 : 15:55:24
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-04 : 16:02:05
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-04 : 16:03:42


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

424 Posts

Posted - 2013-10-04 : 16:18:04
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
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-04 : 17:44:52
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

424 Posts

Posted - 2013-10-08 : 15:36:59
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
   

- Advertisement -