Sorting '1 of 20% item' in Firebird SQL VB.net

Question

0.00/5 (No votes)

See more:

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:

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

I have done numerous sql command but with no luck:

SQL

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

note: UNPAID_FORM is my table - thanks in advance.

makoy

Posted 14-Apr-13 21:25pm

makoy03

Updated 14-Apr-13 21:33pm

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-04-14T21:37:00

Solution 1

Try this:

SQL

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

[EDIT #1]
As mentioned, it's Firebird database engine.

SQL

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

Many interesting functions for Firebird, you'll find here: http://www.volny.cz/iprenosil/interbase/ip_ib_code_string.htm[^]

Posted 14-Apr-13 21:37pm

Maciej Los

Updated 14-Apr-13 23:37pm

v2

Comments

makoy03 15-Apr-13 3:45am

@ Maciej Los: thanks i'll try it now

makoy03 15-Apr-13 4:40am

hello Maciej: I did something like this:

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

and arrived in this kind of error : "Invalid length parameter -1 SubString. Negative INT are not allowed." But when I omit '-1'

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

It returned this error: "Conversion error from string '1 o'". what should i do??

Maciej Los 15-Apr-13 4:51am

Position(... IN ...) - is it Firebird?

makoy03 15-Apr-13 4:52am

yes. it is.

Maciej Los 15-Apr-13 5:01am

You need to use POSITION[^] with SUBSTRING[^] or LEFT[^] function.

Maciej Los 15-Apr-13 5:04am

Try to use CAST[^] twice:

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

makoy03 15-Apr-13 5:23am

HI Maciej: I did combine the position[^] w/ substring[^] same error occurred: "Invalid length parameter -1 SubString. Negative INT are not allowed." But when I omit '-1'

and tried doing cast twice.. both failed - may you still help me, please?

"SELECT * FROM (SELECT CAST(LEFT(PARTICULARS, SUBSTRING(PARTICULARS FROM 1 FOR POSITION('of' IN PARTICULARS)-2))as int) AS ID, PARTICULARS FROM UNPAID_FORM) AS T ORDER BY ID"

Maciej Los 15-Apr-13 5:30am

I do not have Firebird installed, but i have an idea. Replace POSITION('of' IN PARTICULARS) with POSITION(' of' IN PARTICULARS). Do you see the difference?

makoy03 15-Apr-13 5:36am

Yes. I tried doing that earlier, but it returned : conversion error from string "".
I don't why it does returned error like that since that is the most logical way to do it.

makoy03 15-Apr-13 5:26am

I also did this

"SELECT * FROM (SELECT CAST(LEFT(PARTICULARS, SUBSTRING(PARTICULARS FROM 1 FOR 3))as int) AS ID, PARTICULARS FROM UNPAID_FORM) AS T ORDER BY ID"

just returning the error: Conversion error from string '1 o

makoy03 15-Apr-13 5:45am

I think I need to use trim command.

Maciej Los 15-Apr-13 5:48am

If text in PARTICULARS starts with ' ' (space), yes, you need to use TRIM() function.

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

makoy03 15-Apr-13 6:00am

I think I'll take a break my head hurts.

makoy03 15-Apr-13 11:50am

hello Maciej: I did this

SELECT PARTICULARS FROM (SELECT CAST(LEFT(PARTICULARS, POSITION(' of' IN TRIM(' o' from ' of'))+2)AS INT) AS ID, PARTICULARS FROM UNPAID_FORM) AS T ORDER BY ID

and this:

SELECT PARTICULARS FROM (SELECT CAST(LEFT(PARTICULARS, POSITION(' of' IN TRIM(' o' from ' of'))+2)AS INT) AS PARTICULARS FROM UNPAID_FORM) AS T ORDER BY PARTICULARS

I didn't get any error but the database does not sort.

Maciej Los 15-Apr-13 12:29pm

makoy03, you need to isolate ID from PARTICULARS and then sort by ID, not by PARTICULARS!