Error Sorting Strings
in [Programming SQL]
|
Prev: PIVOT data
Next: Change Tracking Help?
From: scott on 25 May 2010 15:04 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
From: J M De Moor on 25 May 2010 16:50 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
From: Gert-Jan Strik on 25 May 2010 17:04 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
From: scott on 25 May 2010 17:18 can you help me with the "second sort criterium ("CASE WHEN ... THEN ... END, catName")" syntax? "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message news:4BFC3B70.CC5C174F(a)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
From: scott on 25 May 2010 17:21
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(a)nospam.net> wrote in message news:ubaKbvE$KHA.4652(a)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 |