sorting character fields

sorting character fields

Post by Steve Rafter » Thu, 12 Mar 1998 04:00:00



Is there a way to sort character fields numerically.
The character fields I am sorting sometimes contain numbers and
sometimes contain characters. If I use the function
to_number(field_name) and a character is in the field I get an
invalid number error and the whole query fails.
--
Steve Raftery

Webvision
19950 Mariner Avenue
Torrance, CA 90503
www.webvision.com

 
 
 

sorting character fields

Post by tzadkie » Thu, 12 Mar 1998 04:00:00


if the field is always going to have numbers only, you can get a numeric
sort by using an lpad.  assuming the field is ten characters long:

select numfield
from tablex
order by lpad(numfield,10)

if the field is a mixture of character and number, try a decode in the
following format:

select numfield
from tablex
order by decode(ltrim(numfield,'1234567890.-
'),'',lpad(numfield,10),numfield)

if the field is numeric, the ltrim will return a null string, and the
decode will evaluate to the lpad, otherwise it will return the field
unaltered.  

the weakness here is that it will try to sort a string like "123..32-4--6"
as a number.  we can avoid that by using a series of nested ltrims to be
more selective about the format:

ltrim(numfield,'- ')  -- strips off the leading minus sign and any spaces

ltrim(ltrim(numfield,'- '),'1234567890')  -- numbers before a decimal

ltrim(ltrim(ltrim(numfield,'- '),'1234567890'),'.')  -- the decimal

ltrim(ltrim(ltrim(ltrim(numfield,'- '),'1234567890'),'.'),'1234567890') --
numbers after the decimal

ltrim(ltrim(ltrim(ltrim(ltrim(numfield,'-
'),'1234567890'),'.'),'1234567890')) -- trailing spaces

a little cumbersome to type, but you can throw it into a database function
called "mix_sort" or something along those lines and sort by the function.

tzad

"uncle tzad's house o' SQL"

ps.  anyone notice that there's an improperly formed numeric string that
will fall through the ltrims as numeric?  what is it and what's the quick
solution?



Quote:> Is there a way to sort character fields numerically.
> The character fields I am sorting sometimes contain numbers and
> sometimes contain characters. If I use the function
> to_number(field_name) and a character is in the field I get an
> invalid number error and the whole query fails.
> --
> Steve Raftery

> Webvision
> 19950 Mariner Avenue
> Torrance, CA 90503
> www.webvision.com


 
 
 

1. Sorting character field numerically?

Greetings,

I have a query that retrieves the next (and subsequent) address from a user
entered address.  For example, someone types in 100, but the first actual
address on this street is 302.  I want to return the 302 (etc.)

The problem is, the addr field is a char(16).  It has to be that way,
because not all addresses are numeric (such as W32 N130 -- fire numbers,
apt. #s, etc.).  Obviously, if the value 1000 exists in the database, that
is dictionarily before 302.  Therefore, the 1000 is sorted first, and that's
the value I get.

Assuming the entered address is strictly numeric (which I check via the VB
code); what I'd like to do is sort all of the addresses numerically,
excluding the obviously non-numeric ones (W32 etc.)  Using the convert()
function, it will error as soon as it encounters on that cannot be
converted.

What I think I need in Sybase is something like VB's VAL() function, which
interprets a string into a number.  If it can't, it makes it 0, and does not
error out.

I figured I could use the between function to make sure the addr field
starts with a character, and exclude all else from the query.  However, that
doesn't exclude something like a duplex, entered as such:  6022-24.  As soon
as it hits the dash, it errors out on the convert() again.

I finally came up with a query that does support that specific case, but
since who knows what will be entered into this field, I cannot
programmatically support all possibilities.

If anybody can think of any functions that I'm overlooking that would help,

explain in English.

Here's the query thus far FYI ONLY; I'm not gonna use it as is (in fact, I'm
not even sure I know why it works at all)...
Thanks,
Dan/PTS
------------
select addr from t_tablename
 where addr between '0000000000000000' and '9999999999999999'
   and street = 20
   and 100 <= case
          when charindex('-', addr) = 0 then convert(numeric, addr)
          when charindex('-', addr) > 0 then convert(numeric,
substring(addr, 1, charindex('-', addr) - 1))
   end
order by case
          when charindex('-', addr) = 0 then convert(numeric, addr)
          when charindex('-', addr) > 0 then convert(numeric,
substring(addr, 1, charindex('-', addr) - 1))
   end

2. Processors on SQL70

3. Sort data alphabetically within a character field

4. Help us protect ourselves

5. numeric sort on character field

6. Error checking in forms 4.5

7. writing 1024 characters in a varchar-field with allows 8000 characters doesn′t work

8. can SQL name be DIFFERENT to computer name?

9. Insert characters to existing character field

10. Space characters in character field

11. Removing duplicates from one field while sorting by a different field

12. Sort by a field that has Date + id fields

13. Create a primary key with a numeric field and a character field