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.
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:aaaAabaCbCase-sensitive:aaaaCbAabTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
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.aspxYour 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 #tmpSELECT x, CHAR(x) FROM cte OPTION (MAXRECURSION 0)-- case sensitive collationSELECT * FROM #tmp ORDER BY x COLLATE SQL_Latin1_General_CP1_CS_AS, id DESC -- case insensitive collationSELECT * 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; |
|
|
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.aspxYour 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 #tmpSELECT x, CHAR(x) FROM cte OPTION (MAXRECURSION 0)-- case sensitive collationSELECT * FROM #tmp ORDER BY x COLLATE SQL_Latin1_General_CP1_CS_AS, id DESC -- case insensitive collationSELECT * 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 |
|
|
|
|
|
|
|