Click here to Skip to main content
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 22:25pm
makoy03280
Edited 14-Apr-13 22:33pm
Maciej Los150.2K
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 275
2 BillWoodruff 260
3 Shweta N Mishra 244
4 Deepu S Nair 230
0 OriginalGriff 6,168
1 Sergey Alexandrovich Kryukov 5,818
2 DamithSL 4,958
3 Manas Bhardwaj 4,539
4 Maciej Los 3,755


Advertise | Privacy | Mobile
Web04 | 2.8.1411019.1 | Last Updated 15 Apr 2013
Copyright © CodeProject, 1999-2014
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