SQL string comparison, greater than and less than operators

by

The comparison operators (including < and >) “work” with string values as well as numbers.

For MySQL

By default, string comparisons are not case sensitive and use the current character set. The default is latin1 (cp1252 West European), which also works well for English.

String comparisons will be case sensitive when the characterset collation of the strings being compared is case sensitive, i.e. the name of the character set ends in _cs rather than _ci. There’s really no point in repeating all of the information that’s available in MySQL Reference Manual here.

MySQL Comparison Operators Reference:
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html

More information about MySQL charactersets/collations:
http://dev.mysql.com/doc/refman/5.5/en/charset.html

To answer the specific questions you asked:

Q: is this a possible way to compare strings in SQL?

A: Yes, in both MySQL and SQL Server

Q: and how does it act?

A: A comparison operator returns a boolean, either TRUE, FALSE or NULL.

Q: a string less than another one comes before in dictionary order? For example, ball is less than water?

A: Yes, because ‘b’ comes before ‘w’ in the characteset collation, the expression 

  'ball' < 'water'

will return TRUE. (This depends on the characterset and on the collation.

Q: and this comparison is case sensitive?

A: Whether a particular comparison is case sensitive or not depends on the database server; by default, both SQL Server and MySQL are case insensitive.

In MySQL it is possible to make string comparisons by specifying a characterset collation that is case sensitive (the characterset name will end in _cs rather than _ci)

Q: For example BALL < water, the upper case character does affect these comparison?

A: By default, in both SQL Server and MySQL, the expression 

  'BALL' < 'water'

would return TRUE.

More Related Contents:

  1. How to split a single column values to multiple column values?
  2. Better techniques for trimming leading zeros in SQL Server?
  3. What’s the best way to capitalise the first letter of each word in a string in SQL Server
  4. How to capitalize the first letter of each word in a string in SQL Server
  5. SQL Server convert string to datetime
  6. Splitting the string in sql server
  7. SQL Server converting varbinary to string
  8. How do I remove extended ASCII characters from a string in T-SQL?
  9. UPDATE and REPLACE part of a string
  10. Find and Replace Column value from Over all Database
  11. SQL query for display one field only once which having multiple record
  12. how to create this table in sql server?
  13. Add a column with a default value to an existing table in SQL Server
  14. What is the best way to paginate results in SQL Server
  15. How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
  16. Multi-statement Table Valued Function vs Inline Table Valued Function
  17. SQL Server Insert if not exists
  18. How do you return the column names of a table?
  19. Dynamically create columns sql
  20. How to set table name in dynamic SQL query?
  21. Rename column SQL Server 2008
  22. Compare DATETIME and DATE ignoring time portion
  23. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  24. How to get the numeric part from a string using T-SQL?
  25. SQL Server SELECT LAST N Rows
  26. update columns values with column of another table based on condition [duplicate]
  27. Why does using an Underscore character in a LIKE filter give me all the results?
  28. Update query using Subquery in Sql Server
  29. “SELECT DISTINCT” ignores different cases
  30. T-SQL: checking for email format

Leave a Comment