SqlServer sorts fields in the specified order

A recent project analyzes a report using a stored procedure. Some names need to be displayed in the specified order, and the numbers corresponding to the names are not listed in the required order. Search for information online and find the charindex function in SQL to solve this problem. But this also has a drawback. When a new name is added

A recent project analyzes a report using a stored procedure. Some names need to be displayed in the specified order, and the numbers corresponding to the names are not listed in the required order. Search for information online and find the charindex function in SQL to solve this problem. But this also has a drawback. When a new name is added

A recent project analyzes a report using a stored procedure. Some names need to be displayed in the specified order, and the numbers corresponding to the names are not listed in the required order. Search for information online and find the charindex function in SQL to solve this problem. However, this method also has a drawback. When a new name is added, the desired result may not be reached and there is no universality.

An example is provided:

Create table test (
Id1 varchar (10 ),
Id2 varchar (10 ),
Name varchar (20)
);
Insert into test values ('20140901', '20160901', 'ccc ');
Insert into test values ('20140901', '20160901', 'bbb ');
Insert into test values ('20140901', '20160901', 'aaa ');
Insert into test values ('20140901', '20160901', 'fff ');
Insert into test values ('20140901', '20160901', 'ddd ');
Insert into test values ('20140901', '20160901', 'eee ');

Query statement:

Select * from dbo. test order by id1, CHARINDEX (id2, '192, 1010, 1003, 1002, 1011,101 ,');

Query Result

Attached charindex function description (from Baidu encyclopedia ):

Syntax

CHARINDEX (expression1, expression2, [start_location])

Parameters

Expression1

An expression that contains the order of characters to be searched.Expression1Is a short character data type classification expression.

Expression2

An expression is usually a column used to search for a specified sequence.Expression2Belongs to the string data type classification.

Start_location

InExpression2SearchingExpression1Start character position. If noStart_locationBut a negative number or zero, thenExpression2Start to search.

Return type

Int