This example provides questions by netizen lang8848 (program mad): I have a table, which is only a field as a character, its content is:
BH
---------
XX1
Xx2
Xx3
Xx4
Xx5
Xx6
Xx7
Xx8
Xx9
XX10
XX11
XX12
...
------------------
I want to sort in this field, and the display format is as follows:
BH
------------
...
XX12
XX11
XX10
Xx9
Xx8
Xx7
Xx6
Xx5
Xx4
Xx3
Xx2
XX1
----------------
Here is a solution to Zou Jianzhu:
Select * from table Oder By Cast (stuff (BH, 1, 2, '') AS INT) DESC
Check the online book, explained the stuff function as follows:
Stuff
Delete the character of the specified length and insert another set of characters in the specified starting point.
grammar
Stuff (Character_Expression, Start, Length, Character_EXPIPRESSION
parameter
Character_EXPIPRESSION
Expressions consisting of character data. The Character_Expression can be a constant, variable, or a column of character or binary data.
Start
It is a inteprect value, specifying the start position of deletion and insertion. If Start or Length is a negative number, return an empty string. If START is longer than the first CHARACTER_EXPRESSION, return to an empty string.
Length
Is an integer that specifies the number of characters to be deleted. If Length is longer than the first CHARACTER_EXPRESSION, the last character is removed to the last character_expression.
Return type
Returns Character Data if character_expression is a supported character data type. Returns binary data if character_expression is a supported binary data type.
Comment
You can nes a string function.
Example
The following example is created and returned to a string by deleting three characters from the second position (character b) in the first string (ABCDEF), then insert a second string in the deleted starting position.
Select Stuff ('Abcdef', 2, 3, 'ijklmn')
Go
The following is the result set:
---------
Aijklmnef
(1 row (s) affected)
