Custom 'Order By' Function?

28 Jul 2005 3:00 PM

hals_left

Hi I have a column varchar(4). Users enter values in one of 5 formats -

1) 1,2,3,4....10,11
2) 01,02,03,04....10,11
3) A1,A2,A3,B1,B2,B3....B10,B11
4) 1.1,2.1,3.1......10.1,11.1
5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B.....10.1,10.1A

The queruies that select from this table will only select records with
one of the formats at any one time. Is it possible to ensure the order
is always logically correct based on numerical and alphabetical
ordering, as above?

So far its seems ok except formats 4 & 5 where I get the folowoing
output-
1.3 1.3A 1.3P 11.3 16.3 2.3 2.3P 2.3S

Thanks
hals_left

28 Jul 2005 3:24 PM

Chandra

Hi

your query will work fine if ur 4 and 5 looks similar to 2.

the results in 4 & 5 are considered and sorted as per the char value.

prefix 0 ans see the results.

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---------------------------------------

Show quote

"hals_left" wrote:

> Hi I have a column varchar(4). Users enter values in one of 5 formats -
>
>
> 1) 1,2,3,4....10,11
> 2) 01,02,03,04....10,11
> 3) A1,A2,A3,B1,B2,B3....B10,B11
> 4) 1.1,2.1,3.1......10.1,11.1
> 5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B.....10.1,10.1A
>
> The queruies that select from this table will only select records with
> one of the formats at any one time. Is it possible to ensure the order
> is always logically correct based on numerical and alphabetical
> ordering, as above?
>
> So far its seems ok except formats 4 & 5 where I get the folowoing
> output-
> 1.3 1.3A 1.3P 11.3 16.3 2.3 2.3P 2.3S
>
> Thanks
> hals_left
>
>

31 Jul 2005 10:19 PM

Hugo Kornelis

On 28 Jul 2005 08:00:54 -0700, hals_left wrote:

>Hi I have a column varchar(4). Users enter values in one of 5 formats -
>
>
>1) 1,2,3,4....10,11
>2) 01,02,03,04....10,11
>3) A1,A2,A3,B1,B2,B3....B10,B11
>4) 1.1,2.1,3.1......10.1,11.1
>5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B.....10.1,10.1A

Hi hals_left,

How does one store 10.1A in a varchar(4) column?

> is thgere now way to write a different
>ordering function?

Try the following. It's not pretty, but it might work:

ORDER BY
CASE
   WHEN my_column LIKE '[A-Z]%'
    THEN LEFT (my_column, 1)
END,
CASE
   WHEN my_column LIKE '[A-Z]%'
    THEN CAST (SUBSTRING (my_column, 2, 3) AS int)
   WHEN my_column LIKE '%.%'
    THEN CAST (LEFT (my_column, CHARINDEX ('.', my_column) - 1) AS int)
    ELSE CAST (my_column AS int)
END,
CASE
   WHEN my_column LIKE '%.%'
    THEN SUBSTRING (my_column, CHARINDEX ('.', my_column) + 1, 4)
END

(untested)

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)