![]() Obviously, the more logic that is added to the ORDER BY clause, the more performance will be affected. Here is the final query.ĬASE WHEN ISNUMERIC(Volume) = 1 THEN CONVERT(nvarchar(20),ĬONVERT(int, Volume)) ELSE Volume END, 20),ĬASE WHEN ISNUMERIC(Series) = 1 THEN CONVERT(nvarchar(20),ĬONVERT(int, Series)) ELSE Series END, 20) The value is not numeric it is sorted as-is. ![]() The solution is to add CASE statements to the ORDER BY clause so that conversions to numeric are only performed if the values are actually numeric. The value "new" cannot be converted to a numeric, so the result is an error. However, in this case, the result is the following error: " Conversion failed when converting the nvarchar value ‘new’ to data type int." This happens because the Series column includes a value of "new" in addition to all of the numeric values. RIGHT(SPACE(20) + CONVERT(nvarchar(20), CONVERT(int, Series)), 20) The prevoius method of converting the Series column to a numeric value and then back to nvarchar is shown here. INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ‘1’, ’02’) Now add some ‘dirty’ data to the Series column. RIGHT(SPACE(20) + CONVERT(nvarchar(20), CONVERT(int, Volume)), 20), This is accomplished by converting the values to numeric and then back to nvarchar during the sort operation, like so: The following query builds on the first example and handles the dirty data by removing the leading zeros. INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ’02’, ”) INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ‘012’, ”) This new data is numeric values with leading zeros. Here are the results:įor the next example, add some ‘dirty’ data to the Volume column. If you know that you have clean data such as this, this simple query will sort the string values in the Volume and Series columns as numerics.Īs you can see, this was achieved by padding each column with its maximum length (20 characters) in spaces, and then sorting by the rightmost 20 characters of each value. INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ‘4’, ”) INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ‘1’, ‘1’) INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ‘5’, ”) INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ‘1’, ‘new’) INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ‘1’, ‘3’) INSERT #Book (Title, Volume, Series) VALUES (‘Journal of Sloths’, ’13’, ”) ![]() For our sample table, there are two columns with numeric data stored as strings: Volume and Series.īookID int identity(1,1) NOT NULL PRIMARY KEY, The data is "clean" because it is uncomplicated by leading zeros or spaces. Be sure to perform your own evaluations before using these in production environments.įor the first example, set up a sample table and some relatively clean test data. One caveat I have not performed extensive performance testing or examined query plans to get an idea of the efficiency of these queries. The examples below will sort this sequence as "1", "2", "11" instead. Following are some tricks that I have found useful for sorting numeric data that is stored as strings.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |