Click here to Skip to main content
11,705,212 members (43,677 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL VB.NET
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:

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
makoy03280
Edited 14-Apr-13 21:33pm
Maciej Los193.7K
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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

[EDIT #1]
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

Many interesting functions for Firebird, you'll find here: http://www.volny.cz/iprenosil/interbase/ip_ib_code_string.htm[^]
  Permalink  
v2
Comments
makoy03 at 15-Apr-13 3:45am
   
@ Maciej Los: thanks i'll try it now
makoy03 at 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 at 15-Apr-13 4:51am
   
Position(... IN ...) - is it Firebird?
makoy03 at 15-Apr-13 4:52am
   
yes. it is.
Maciej Los at 15-Apr-13 5:01am
   
You need to use POSITION[^] with SUBSTRING[^] or LEFT[^] function.
Maciej Los at 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 at 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 at 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 at 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 at 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 at 15-Apr-13 5:45am
   
I think I need to use trim command.
Maciej Los at 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 at 15-Apr-13 6:00am
   
I think I'll take a break my head hurts.
makoy03 at 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 at 15-Apr-13 12:29pm
   
makoy03, you need to isolate ID from PARTICULARS and then sort by ID, not by PARTICULARS!

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 535
1 OriginalGriff 244
2 ppolymorphe 116
3 CPallini 82
4 Inimicos 60
0 OriginalGriff 8,988
1 Sergey Alexandrovich Kryukov 8,272
2 CPallini 5,189
3 Maciej Los 4,726
4 Mika Wendelius 3,606


Advertise | Privacy | Mobile
Web02 | 2.8.150819.1 | Last Updated 15 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100