I'm trying to sort the below table by numbers first, then string-only 
records. The numeric records will come first, a double digit "10" sorts 
before a single digit "4", etc. I've tried several suggested methods but I'm 
getting a string conversion error. Can someone take a look at this DDL and 
explain how can I test each string to see if it begins with a number and 
then sort as I described?

The error is "Error converting data type varchar to numeric."


DDL
================

IF object_id('tempdb..#tmpMyTable') IS NOT NULL

BEGIN

DROP TABLE #tmpMyTable

END

BEGIN

CREATE TABLE #tmpMyTable (

catID int identity(1,1) NOT NULL,

catName varchar(50) NULL

);

END

BEGIN

SET NOCOUNT ON

INSERT INTO #tmpMyTable(catName) VALUES('10" Foliage')

INSERT INTO #tmpMyTable(catName) VALUES('12" Foliage')

INSERT INTO #tmpMyTable(catName) VALUES('4" Foliage')

INSERT INTO #tmpMyTable(catName) VALUES('6" Foliage')

INSERT INTO #tmpMyTable(catName) VALUES('8" Foliage')

INSERT INTO #tmpMyTable(catName) VALUES('Dish Gardens')

INSERT INTO #tmpMyTable(catName) VALUES('Blooming')

INSERT INTO #tmpMyTable(catName) VALUES('Poinsettias')

INSERT INTO #tmpMyTable(catName) VALUES('Misc')

END

SELECT *

FROM #tmpMyTable

ORDER BY CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 1

THEN CAST(Replace( catName, '"', '') AS decimal(18,4))

ELSE catName

END

DROP TABLE #tmpMyTable

Scott

> I'm trying to sort the below table by numbers first, then string-only
> records. The numeric records will come first, a double digit "10" sorts
> before a single digit "4", etc.

Didn't you just ask a question almost exactly like this?  The messy way 
is to substring out and cast the leading numeric...similar to the way a 
couple of us showed you previously.

The cleaner way is to change the table itself and remove the size (and 
unit of measure) from the catName, as they really should be considered 
separate attributes, like shirt size would be.  E.g., how is the sorting 
going to work once you start categorizing Poinsettias by the inch?  Do 
you really want to sort all the 4" items before 6" ones, etc.?

Joe

Scott,

A CASE expression has a data type. Or rather, just one data type.
Because of the cast to decimal in the THEN clause, it considers the data
type to be decimal. It then complains about values in your ELSE clause,
then they cannot be converted to numeric.

That is because none of your values are numbers. Even if you strip the
double quotes out of '10" Foliage', you still end up with '10 Foliage',
which obviously isn't a number. You need to clean up your data to be
able to order the results in a simple manner.

You can avoid the conversion error by removing the ELSE clause. If
needed, you can always add a second sort criterium ("CASE WHEN ... THEN
.... END, catName").

-- 
Gert-Jan



scott wrote:
> 
> I'm trying to sort the below table by numbers first, then string-only
> records. The numeric records will come first, a double digit "10" sorts
> before a single digit "4", etc. I've tried several suggested methods but I'm
> getting a string conversion error. Can someone take a look at this DDL and
> explain how can I test each string to see if it begins with a number and
> then sort as I described?
> 
> The error is "Error converting data type varchar to numeric."
> 
> DDL
> ================
> 
> IF object_id('tempdb..#tmpMyTable') IS NOT NULL
> 
> BEGIN
> 
> DROP TABLE #tmpMyTable
> 
> END
> 
> BEGIN
> 
> CREATE TABLE #tmpMyTable (
> 
> catID int identity(1,1) NOT NULL,
> 
> catName varchar(50) NULL
> 
> );
> 
> END
> 
> BEGIN
> 
> SET NOCOUNT ON
> 
> INSERT INTO #tmpMyTable(catName) VALUES('10" Foliage')
> 
> INSERT INTO #tmpMyTable(catName) VALUES('12" Foliage')
> 
> INSERT INTO #tmpMyTable(catName) VALUES('4" Foliage')
> 
> INSERT INTO #tmpMyTable(catName) VALUES('6" Foliage')
> 
> INSERT INTO #tmpMyTable(catName) VALUES('8" Foliage')
> 
> INSERT INTO #tmpMyTable(catName) VALUES('Dish Gardens')
> 
> INSERT INTO #tmpMyTable(catName) VALUES('Blooming')
> 
> INSERT INTO #tmpMyTable(catName) VALUES('Poinsettias')
> 
> INSERT INTO #tmpMyTable(catName) VALUES('Misc')
> 
> END
> 
> SELECT *
> 
> FROM #tmpMyTable
> 
> ORDER BY CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 1
> 
> THEN CAST(Replace( catName, '"', '') AS decimal(18,4))
> 
> ELSE catName
> 
> END
> 
> DROP TABLE #tmpMyTable

can you help me with the "second sort criterium ("CASE WHEN ... THEN ... 
END, catName")" syntax?


"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
news:4BFC3B70.CC5C174F@xs4all.nl...
> Scott,
>
> A CASE expression has a data type. Or rather, just one data type.
> Because of the cast to decimal in the THEN clause, it considers the data
> type to be decimal. It then complains about values in your ELSE clause,
> then they cannot be converted to numeric.
>
> That is because none of your values are numbers. Even if you strip the
> double quotes out of '10" Foliage', you still end up with '10 Foliage',
> which obviously isn't a number. You need to clean up your data to be
> able to order the results in a simple manner.
>
> You can avoid the conversion error by removing the ELSE clause. If
> needed, you can always add a second sort criterium ("CASE WHEN ... THEN
> ... END, catName").
>
> -- 
> Gert-Jan
>
>
>
> scott wrote:
>>
>> I'm trying to sort the below table by numbers first, then string-only
>> records. The numeric records will come first, a double digit "10" sorts
>> before a single digit "4", etc. I've tried several suggested methods but 
>> I'm
>> getting a string conversion error. Can someone take a look at this DDL 
>> and
>> explain how can I test each string to see if it begins with a number and
>> then sort as I described?
>>
>> The error is "Error converting data type varchar to numeric."
>>
>> DDL
>> ================
>>
>> IF object_id('tempdb..#tmpMyTable') IS NOT NULL
>>
>> BEGIN
>>
>> DROP TABLE #tmpMyTable
>>
>> END
>>
>> BEGIN
>>
>> CREATE TABLE #tmpMyTable (
>>
>> catID int identity(1,1) NOT NULL,
>>
>> catName varchar(50) NULL
>>
>> );
>>
>> END
>>
>> BEGIN
>>
>> SET NOCOUNT ON
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('10" Foliage')
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('12" Foliage')
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('4" Foliage')
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('6" Foliage')
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('8" Foliage')
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('Dish Gardens')
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('Blooming')
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('Poinsettias')
>>
>> INSERT INTO #tmpMyTable(catName) VALUES('Misc')
>>
>> END
>>
>> SELECT *
>>
>> FROM #tmpMyTable
>>
>> ORDER BY CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 1
>>
>> THEN CAST(Replace( catName, '"', '') AS decimal(18,4))
>>
>> ELSE catName
>>
>> END
>>
>> DROP TABLE #tmpMyTable 

I'm in a bind because we may need to add a 6' ( 6 foot) item at some point 
and I won't know whether to display a alpha-numeric value with the inch sign 
or a foot sign.

I just thought there should be a way to sort the alpha-numeric values and 
then string-only values.


"J M De Moor" <papajoe.nospam@nospam.net> wrote in message 
news:ubaKbvE$KHA.4652@TK2MSFTNGP06.phx.gbl...
> Scott
>
>> I'm trying to sort the below table by numbers first, then string-only
>> records. The numeric records will come first, a double digit "10" sorts
>> before a single digit "4", etc.
>
> Didn't you just ask a question almost exactly like this?  The messy way is 
> to substring out and cast the leading numeric...similar to the way a 
> couple of us showed you previously.
>
> The cleaner way is to change the table itself and remove the size (and 
> unit of measure) from the catName, as they really should be considered 
> separate attributes, like shirt size would be.  E.g., how is the sorting 
> going to work once you start categorizing Poinsettias by the inch?  Do you 
> really want to sort all the 4" items before 6" ones, etc.?
>
> Joe 

scott (sbailey@mileslumber.com) writes:
> can you help me with the "second sort criterium ("CASE WHEN ... THEN ... 
> END, catName")" syntax?
 
Rather than having

   CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 1
        THEN CAST(Replace( catName, '"', '') AS decimal(18,4))
        ELSE catName
   END

You need something like:

   CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 1
        THEN CAST(Replace( catName, '"', '') AS decimal(18,4))
   END,
   CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 0
        THEN catName
   END




-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

That's almost except the alpha-numeric values are sorting as "10, 12, 4, 6, 
8" instead of "4, 6, 8, 10, 12".




"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D84CD83AB4Yazorman@127.0.0.1...
> scott (sbailey@mileslumber.com) writes:
>> can you help me with the "second sort criterium ("CASE WHEN ... THEN ...
>> END, catName")" syntax?
>
> Rather than having
>
>   CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 1
>        THEN CAST(Replace( catName, '"', '') AS decimal(18,4))
>        ELSE catName
>   END
>
> You need something like:
>
>   CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 1
>        THEN CAST(Replace( catName, '"', '') AS decimal(18,4))
>   END,
>   CASE WHEN IsNumeric(Replace( catName, '"', '' )) = 0
>        THEN catName
>   END
>
>
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 

scott wrote:
> I'm in a bind because we may need to add a 6' ( 6 foot) item at some
> point and I won't know whether to display a alpha-numeric value with
> the inch sign or a foot sign.

That'll be a 72" item then?

-- 
Andrew 

>> That will be a 72" item then? <<

 .. which can be converted to feet-inch in the front end where display
formatting is supposed to be done. Yet another reason we need to go
Metric ..