How to sort table with varchar column having integer value with special characters

Question

how to sort a table with a varchar column having an integer value with special characters I have a table in the database that has a column of type varchar and values ​​Here I tried with mysql query

   SELECT * FROM table order by CONVERT(seq_no,signed integer) ASC

Here I get this result.

    1
    2
    3
    4
    5
    6
    6_1
    6_2
    6_1_1
    6_1_2
    7
    8
    8_1
    8_2
    8_1_1
    8_1_2
    8_2_1
    8_2_2

But the result should look like this.

    1
    2
    3
    4
    5
    6
    6_1
    6_1_1
    6_1_2
    6_2
    7
    8
    8_1
    8_1_1
    8_1_2
    8_2
    8_2_1
    8_2_2

I also tried with this query, but did not get the exact result.

   SELECT * FROM table order by CONVERT(seq_no,signed integer) ASC,seq_no

Answer 1

You can try something like this:

select * 
from table
where id = 18
order by cast(replace(INSERT(colname, LOCATE('_', colname), CHAR_LENGTH('_'), '.'),'_','') as decimal(10,4))

How it works:

• First, replace only the first underline with a dot / dot
• Then remove all other underscores from the value.
• , ,

. "6_1_1" "6.1_1", "6.11". 6.1, "6_1" , 6.12 ( "6_1_2" ), 10.

: !

SQL Fiddle

Answer 2

, . , 10 :

6_1_2 ->           000000000600000000010000000002
6123_1111_33333 -> 000000612300000011110000033333
1123_5555       -> 000000112300000055550000000000

. SUBSTRING_INDEX() LPAD() :

SELECT * 
FROM Table1 
ORDER BY

CONCAT
(
LPAD(SUBSTRING_INDEX( concat(seq_no,'_') , '_', 1 ),10,'0')
,
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX( concat(seq_no,'_') , '_', 2 ),'_',-1),10,'0') 
,
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX( concat(seq_no,'_') , '_', 3 ),'_',-1),10,'0') 
,
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX( concat(seq_no,'_') , '_', 4 ),'_',-1),10,'0') 
)

SQLFiddle

Answer 3

: -

;WITH CTE AS
(
 SELECT BatchNumber, CAST(CONCAT('<A>',REPLACE(BatchNumber,'_','</A><A>'),'</A>') AS XML) Xmlcol 
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk 
 FROM ApplicationBatchNumbers
)
,CTE1 AS
(
SELECT BatchNumber, SplittedString , rnk , ROW_NUMBER() OVER (PARTITION BY RNK ORDER BY (SELECT NULL)) finalrnk 
FROM
    (SELECT * FROM CTE) s
CROSS APPLY
    (
        SELECT ProjectData.D.value('.', 'smallint') as SplittedString
        FROM s.xmlcol.nodes('A') as ProjectData(D)
    ) a
)
SELECT BatchNumber FROM CTE1
PIVOT ( MAX(SplittedString) FOR finalrnk IN ([1],[2],[3]) ) pvt