在Firebird SQL VB.net中排序“20％项目中的1” [英] Sorting '1 of 20% item' in Firebird SQL VB.net

问题描述

有人请帮助我。我试图在Firebird 2.1数据库中排序（自然顺序） - 一个名为''PARTICULARS'的列 - varchar（35），其记录如下：

Would anyone please help me. I trying to sort out (natural order) in a Firebird 2.1 database - a column named ''PARTICULARS'' - varchar(35) that has record like this:

'1 of 20% item'
'10 of 20% item'
'2 of 20% item'
'5 of 20% item'
.
.
.

to：

to:

'1 of 20% item'
'2 of 20% item'
'5 of 20% item'
'10 of 20% item'

我做了很多sql命令，但没有运气：

I have done numerous sql command but with no luck:

select * from UNPAID_FORM order by cast(Case patIndex('', LTrim (PARTICULARS)) when 0 then LTrim (PARTICULARS) else substring (LTrim (PARTICULARS), 1, patIndex ('', LTrim (PARTICULARS)) - 1)End as int) asc

select * from UNPAID_FORM order by substring(PARTICULARS from 1 for patindex ('', LTrim (PARTICULARS)-1)

select PARTICULARS from UNPAID_FORM order by 1 nulls last

注意：UNPAID_FORM是我的桌子 - 提前谢谢。



makoy

note: UNPAID_FORM is my table - thanks in advance.

makoy

推荐答案

试试这个：

Try this:

SELECT *
FROM (
    SELECT CONVERT(INT, LEFT(PARTICULARS, CHARINDEX('of',PARTICULARS)-1)) AS ID, PARTICULARS
    FROM UNPAID_FORM
    ) AS T
ORDER BY ID

结果：

Result:

1   1 of 20% item
2   2 of 20% item
5   5 of 20% item
10  10 of 20% item

如上所述，它' Firebird数据库引擎。

As mentioned, it''s Firebird database engine.

SELECT * FROM (
    SELECT CAST(LEFT(PARTICULARS, POSITION(' of' IN PARTICULARS)) AS INT) AS ID, PARTICULARS
    FROM UNPAID_FORM
    ) AS T
ORDER BY ID

Firebird的许多有趣功能，你会在这里找到： http://www.volny.cz/iprenosil/interbase/ip_ib_code_string.htm [ ^ ]

这篇关于在Firebird SQL VB.net中排序“20％项目中的1”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！