Error Sorting Strings
in [Programming SQL]
|
Prev: PIVOT data
Next: Change Tracking Help?
From: Erland Sommarskog on 25 May 2010 18:04 scott (sbailey(a)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(a)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
From: scott on 25 May 2010 18:37 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(a)sommarskog.se> wrote in message news:Xns9D84CD83AB4Yazorman(a)127.0.0.1... > scott (sbailey(a)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(a)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 >
From: Geoff Schaller on 25 May 2010 19:07 Scott, This is your issue, not sql server's. The best way to sort numeric strings is to prefix the number with a defined number of zeros. For example, if the largest number is 5 digits wide then: 1 20 345 11 Becomes 00001 00020 00345 00011 ....and then they will sort. So what you do is go right('00000' + number.asstring, 5) (in pseudo code to give you the idea) Geoff Schaller Software Objectives "scott" <sbailey(a)mileslumber.com> wrote in message news:uhfxgrF$KHA.5560(a)TK2MSFTNGP02.phx.gbl: > 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(a)sommarskog.se> wrote in message > news:Xns9D84CD83AB4Yazorman(a)127.0.0.1... > > > scott (sbailey(a)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(a)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 > >
From: scott on 25 May 2010 19:25 I kind of understand what your saying, but I'm now just trying to create a sort column named "sort_size" below. If you run the code below, you'll see that I'm not effecting any of the values. I even have a function that will return only numbers from strings, but I'm having a problem testing each value and then either converting it to a number or leaving it as a string. How can I modify the CASE WHEN syntax to effect only the alpha-numeric values and leave the string-only values as strings? 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 catID, catName, CASE WHEN IsNumeric(Replace(catName, '"', '' )) = 1 THEN right('00000' + catName, 5) -- CAST(Replace(catName, '"', '') AS decimal(18,4)) END, CASE WHEN IsNumeric(Replace(catName, '"', '' )) = 0 THEN catName END AS sort_size FROM #tmpMyTable ORDER BY CASE WHEN IsNumeric(Replace(catName, '"', '' )) = 1 THEN CAST(Replace(catName, '"', '') AS decimal(18,4)) END, CASE WHEN IsNumeric(Replace(catName, '"', '' )) = 0 THEN catName END DROP TABLE #tmpMyTable "Geoff Schaller" <geoffx(a)softxwareobjectives.com.au> wrote in message news:BKYKn.27162$pv.15901(a)news-server.bigpond.net.au... > Scott, > > This is your issue, not sql server's. The best way to sort numeric strings > is to prefix the number with a defined number of zeros. For example, if > the largest number is 5 digits wide then: > > 1 > 20 > 345 > 11 > > Becomes > > 00001 > 00020 > 00345 > 00011 > > ...and then they will sort. > > So what you do is go right('00000' + number.asstring, 5) > (in pseudo code to give you the idea) > > Geoff Schaller > Software Objectives > > > > "scott" <sbailey(a)mileslumber.com> wrote in message > news:uhfxgrF$KHA.5560(a)TK2MSFTNGP02.phx.gbl: > >> 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(a)sommarskog.se> wrote in message >> news:Xns9D84CD83AB4Yazorman(a)127.0.0.1... >> >> > scott (sbailey(a)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(a)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 >> > >
From: Plamen Ratchev on 25 May 2010 21:32
Here is one method. This is not perfect but works based on your sample data: SELECT catID, catName, CASE WHEN CHARINDEX('"', catName) > 0 THEN RIGHT('00' + LEFT(catName, CHARINDEX('"', catName)), 3) ELSE catName END AS sort_size FROM #tmpMyTable ORDER BY sort_size; -- Plamen Ratchev http://www.SQLStudio.com |