Method One:
For example, the field name in the SQL table needs to be sorted as follows:
Zhang San (Z)
John Doe (L)
Harry (W)
Zhao Liu (Z)
According to the default collation in SQL, sorted alphabetically (a~z), the result is: John Doe Harry Zhaolizhang
Custom Sort: Order Bycharindex (NAME, ' Dick and Harry Harry Zhao Liu ')
The CHARINDEX function returns the starting position of a character or string within another string. The CHARINDEX function call method is as follows:
CHARINDEX (expression1, expression2 [, Start_location])
Expression1 is the character to look for in expression2, Start_location is where the CHARINDEX function begins to find expression2 in expression1.
The CHARINDEX function returns an integer that is the position of the string to find in the string being searched for. If CHARINDEX does not find the string to find, then the function integer "0". Let's take a look at the result of the following function command execution:
CHARINDEX (' sql ', ' Microsoft SQL Server ')
This function command returns the starting position of "SQL" in "Microsoft SQL Server", in which case the CHARINDEX function returns "S" in Microsoft SQL Server location 11.
Next, let's look at this charindex command:
CHARINDEX (' 7.0 ', ' Microsoft SQL Server 2000 ')
In this example, CHARINDEX returns zero because the string "7.0" cannot be found in Microsoft SQL Server. Next, let's take a look at two examples of how to use the CHARINDEX function to solve the actual T-SQL problem. Method Two: Sort the class field for table Table_example, the Class field value is: A, B, C, D. The user requires that the data in table_example be sorted in the order of the class field values C, A, D, B.
SELECT * FROM Teble_exaple
ORDER BY (
Case class
When the ' C ' then 1,
When ' A ' and then 2,
When the ' D ' then 3,
When ' B ' then 4
Else '
End
Method Three: Using the Decode function: SELECT * from Table_example Order by Decode (class, ' C ', 1, ' A ', 2, ' D ', 3, ' B ', 4)
SQL Custom Sorting
