How do I get SQL sorted in array order

ids = "1,2,3,4,5,6";

Sql= "Select Bookid,bookname from Books where BookID on (" + IDs + ") Order by CHARINDEX (BookID," +ids+ ")"

The CHARINDEX function returns an integer that returns an integer that is the position of the string to be found in the string being searched for

BookID is going to find the location in IDs

The following is an online search.

Order by commonly used methods I won't mention it.

The changing needs of the project
Let's take a look at some of the weird ordering requirements

--Create a table first
CREATE TABLE AI (
ID int NOT NULL,
No varchar (TEN) NOT NULL
)
Go

--inserting data into the table
INSERT INTO AI
Select 105, ' 2 '
UNION ALL
Select 105, ' 1 '
UNION ALL
Select 103, ' 1 '
UNION ALL
Select 105, ' 4 '
Go

--query results are as follows:
SELECT * from AI
Go
ID no
----------- ----------
105 2
105 1
103 1
105 4

I.
-The required query results are as follows
-that is, the data of the No column is ordered by ' 4 ', ' 1 ', ' 2 '
ID no
----------- ----------
105 4
105 1
103 1
105 2

--Solution 1
--Using function charindex
SELECT * from AI
ORDER BY CHARINDEX (No, ' 4,1,2 ')

--Solution 2, and each group is then sorted in descending order of ID
--Using the function case
SELECT * from AI
Order by case when no= ' 4 ' and then 1
When no= ' 1 ' then 2
When no= ' 2 ' then 3
End,id desc

--Solution 3
--Using the Union operator
SELECT * from AI
where no= ' 4 '
UNION ALL
SELECT * from AI
where no= ' 1 '
UNION ALL
SELECT * from AI
where no= ' 2 '

Ii.
--Query request specifies the first row of the no= ' 4 ' row, the other rows are randomly sorted
ID no
----------- ----------
105 4
105 2
105 1
103 1

--Solutions
SELECT * from AI
Order by case when no= ' 4 ' and then 1
else 1+rand ()
End

Iii.
--query requires random ordering of all rows

--Solutions
SELECT * from AI
Order by NEWID ()

Iiii
-There is a table ab with column I, where the data is as follows:
I varchar (10)
A1
A10
A101
A5
P4
P41
P5

--now requires that the data in column I be sorted alphabetically and then sorted by numbers
-The effect is as follows:
A1
A5
A10
A101
P4
P5
P41

--Solutions
SELECT * FROM AB
Order by Left (i,1), convert (int,substring (i,2,8000))