SQL sort on text field 
Author Message
 SQL sort on text field

I have a table that stores numeric sizes as text (some users might want to
put non-numeric characters in the field).  When I do a sort, such as this:
    "SELECT * FROM tablename ORDER BY Size ASC"
I get an alphanumeric sort where 10 comes before 2.

If I change my SQL to include the VB-native Val() function:
    "SELECT * FROM tablename ORDER BY val(Size) ASC"
I get the correct, numeric sorting order (where 10 comes after 2), but a
blank (Null, actually) in the Size field causes an error.

Is there any way for me to do this?



Sun, 16 Jul 2000 03:00:00 GMT  
 SQL sort on text field


Quote:
>I have a table that stores numeric sizes as text (some users might want to
>put non-numeric characters in the field).  When I do a sort, such as this:
>    "SELECT * FROM tablename ORDER BY Size ASC"
>I get an alphanumeric sort where 10 comes before 2.

>If I change my SQL to include the VB-native Val() function:
>    "SELECT * FROM tablename ORDER BY val(Size) ASC"
>I get the correct, numeric sorting order (where 10 comes after 2), but a
>blank (Null, actually) in the Size field causes an error.

>Is there any way for me to do this?

Try this:

SELECT * FROM tablename ORDER BY IIf(IsNumeric(Size),val(Size),0) ASC

Hope this helps,

Hope this helps,
Tim "Woof" Gray (remove NOSPAM from email address)
Join us in the Visual Basic and Database Chat every Tuesday 9:30pm EST at:
http://msncomputing.msn.com/forums/default.asp?softwaredevelopment/ch...
or point MS Chat to server "publicchat.msn.com" and meet us in room
"SOFTWARE_DEV_TALKSHOP".



Sun, 16 Jul 2000 03:00:00 GMT  
 SQL sort on text field

Hi,
 IMHO --1)Try using  not isnull function.on that field in the where clause.
Check your database sql syntax for that.

2) If it is MS Access then you can try this also
   where len(trim(fieldName) & "" ) > 0

Thank you,
Surya



Quote:
> I have a table that stores numeric sizes as text (some users might want
to
> put non-numeric characters in the field).  When I do a sort, such as
this:
>     "SELECT * FROM tablename ORDER BY Size ASC"
> I get an alphanumeric sort where 10 comes before 2.

> If I change my SQL to include the VB-native Val() function:
>     "SELECT * FROM tablename ORDER BY val(Size) ASC"
> I get the correct, numeric sorting order (where 10 comes after 2), but a
> blank (Null, actually) in the Size field causes an error.

> Is there any way for me to do this?



Mon, 17 Jul 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. SQL sort on text field

2. Loading Rich Text Box from SQL Server 6.5 Text Field

3. URGENT: Sorting Text Fields

4. Sorting Text Fields

5. Having problems with sorting character or text fields

6. Sorting Numbers in Text Field

7. Sort on Text Field

8. Sorting Text fields in Numeric Order

9. Sorting Numbers in Text Field

10. VB Variables, SQL Server to Text Form Fields

11. SQL Text Fields/Display-edit on page

12. Assign SQL expression to text field in Access ?

 

 
Powered by phpBB® Forum Software