Prev: A severe error occurred on the current command. The results,if any, should be discarded
Next: concatenating and two decimal places
From: scott on 22 May 2010 13:48
I have a field named itemSize that contains values like the below example.
When I do a regular "ORDER BY" itemSize, SQL sorts the 10" through 14"
first, then it goes to 4" through 8" and finishes with the "n/a". In vb, I
have used the Val() function to overcome this issue, but SQL doesn't contain
a Val() function.

What would be the best way to drop the inch character and get a true sort?
Do I need to pad a "0" to the single digit records? I need to be able to
sort asc and desc. Any ideas?



itemSize
==========
n/a
4"
5.5"
6"
14"
8"
10"
10.5"



From: J M De Moor on 22 May 2010 14:24
Scott

>
> What would be the best way to drop the inch character and get a true sort?
> Do I need to pad a "0" to the single digit records? I need to be able to
> sort asc and desc. Any ideas?
>

This may not be the most elegant answer you get but it works for me...

CREATE TABLE Sizes (
foo_size VARCHAR(5) NOT NULL PRIMARY KEY
);

INSERT INTO Sizes VALUES('n/a');
INSERT INTO Sizes VALUES('4"');
INSERT INTO Sizes VALUES('5.5"');
INSERT INTO Sizes VALUES('6"');
INSERT INTO Sizes VALUES('14"');
INSERT INTO Sizes VALUES('8"');
INSERT INTO Sizes VALUES('10"');
INSERT INTO Sizes VALUES('10.5"');

SELECT
CASE
WHEN foo_size = 'n/a' THEN 0.0
ELSE CAST(LEFT(foo_size, LEN(foo_size) - 1) AS NUMERIC(3, 1))
END AS sort_size
FROM Sizes
ORDER BY sort_size;


Joe De Moor
From: scott on 22 May 2010 15:13
Works fine. Thanks sir.


"J M De Moor" <papajoe.nospam(a)nospam.net> wrote in message
news:etRFKwd%23KHA.348(a)TK2MSFTNGP06.phx.gbl...
> Scott
>
>>
>> What would be the best way to drop the inch character and get a true
>> sort?
>> Do I need to pad a "0" to the single digit records? I need to be able to
>> sort asc and desc. Any ideas?
>>
>
> This may not be the most elegant answer you get but it works for me...
>
> CREATE TABLE Sizes (
> foo_size VARCHAR(5) NOT NULL PRIMARY KEY
> );
>
> INSERT INTO Sizes VALUES('n/a');
> INSERT INTO Sizes VALUES('4"');
> INSERT INTO Sizes VALUES('5.5"');
> INSERT INTO Sizes VALUES('6"');
> INSERT INTO Sizes VALUES('14"');
> INSERT INTO Sizes VALUES('8"');
> INSERT INTO Sizes VALUES('10"');
> INSERT INTO Sizes VALUES('10.5"');
>
> SELECT
> CASE
> WHEN foo_size = 'n/a' THEN 0.0
> ELSE CAST(LEFT(foo_size, LEN(foo_size) - 1) AS NUMERIC(3, 1))
> END AS sort_size
> FROM Sizes
> ORDER BY sort_size;
>
>
> Joe De Moor


From: Gert-Jan Strik on 23 May 2010 04:37
Scott,

In case Joe's solution doesn't always work for you, you could consider
something like this:

SELECT *
FROM Sizes
ORDER BY CASE WHEN IsNumeric(Replace( foo_size, '"', '' )) = 1
THEN CAST(Replace( foo_size, '"', '') AS decimal(18,4))
END

If you want "n/a" to appear at the end, then add "ELSE
99999999999999.9999" to the CASE.

--
Gert-Jan


scott wrote:
>
> I have a field named itemSize that contains values like the below example.
> When I do a regular "ORDER BY" itemSize, SQL sorts the 10" through 14"
> first, then it goes to 4" through 8" and finishes with the "n/a". In vb, I
> have used the Val() function to overcome this issue, but SQL doesn't contain
> a Val() function.
>
> What would be the best way to drop the inch character and get a true sort?
> Do I need to pad a "0" to the single digit records? I need to be able to
> sort asc and desc. Any ideas?
>
> itemSize
> ==========
> n/a
> 4"
> 5.5"
> 6"
> 14"
> 8"
> 10"
> 10.5"
From: --CELKO-- on 23 May 2010 21:17
On May 22, 12:48 pm, "scott" <sbai...(a)mileslumber.com> wrote:
> I have a field named itemSize that contains values like the below example..
> When I do a regular "ORDER BY" itemSize, SQL sorts the 10" through 14"
> first, then it goes to 4" through 8" and finishes with the "n/a". In vb, I
> have used the Val() function to overcome this issue, but SQL doesn't contain
> a Val() function.
>
> What would be the best way to drop the inch character and get a true sort?
> Do I need to pad a "0" to the single digit records? I need to be able to
> sort asc and desc. Any ideas?
>
> itemSize
> ==========
> n/a
> 4"
> 5.5"
> 6"
> 14"
> 8"
> 10"
> 10.5"

 |  Next  |  Last
Pages: 1 2
Prev: A severe error occurred on the current command. The results,if any, should be discarded
Next: concatenating and two decimal places