Sort Alpha Numeric data in SQL Server

Sorting a alphanumeric data, A sample data is as below

 image002.jpg

 When i sort the data it looks like below
image003.jpg

 I have modified the ORDER BY clause as shown below, and it returned the results in the proper order.

 (Note: The ID column is defined as varchar(20). So, I did the following to fix this issue:

 If ID is numeric, add 21 '0's in front of the ID value and get the last 20 characters

If ID is not numeric, add 21 ‘’s at the end of the ID value and get the first 20 characters
image004.jpg

Comments

Post a Comment

Your comment or feedback help me to write more blogs. Please add your comments.

Popular posts from this blog

How to Stop Excel from giving a number in scientific notation

If you ever enter a very large number into a cell, you'll notice that Microsoft Excel displays it in scientific notation. For example, when you enter the number 1231231231234 in a cell, Excels displays 1.23123E+12. No matter how large you make the cell width, Excel still displays a number larger than 12 characters in scientific notation. To display the number in a format other than scientific notation, you need to reformat the cell(s). Here's how: We have two methods to get rid of this issue. Method 1 Concate the scientific notation with a single quote('), you will get the proper number. 1.11E+19 '11111111111111100000 But we have a issue still exists if it cross 20, here is an example. 1.11E+20 '1.11111111111111E+20 Disadvantage: So you cannot use method1 if it cross 20. Method 2 Step1:  Select the scientific notation , Go to format cell, select Text Step2:  Select the scientific notat...
Read more

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

This error bugged me for a whole day, I google a lotz and read many articles, but no use i was not able to fix this issue. I am using Windows 7 64bit O/S with 32bit Ms Office, i uninstalled Office completely and downloaded AccessDatabaseEngine_X64 and installed and run my application it worked smoothly. Then again i uninstalled microsoft access database engine and tried, i was getting that error message. So i concluded that i have to use Access database engine x 64 which was not installed during my ms office installation. you can download the same from the below URL. 64bit http://download.microsoft.com/download/f/d/8/fd8c20d8-e38a-48b6-8691-542403b91da1/AccessDatabaseEngine.exe 32bit http://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine.exe But this did not resolved my problem, so i relayed on third party component(Infragistics).
Read more

System.IO.IsolatedStorage.IsolatedStorageException: Unable to create the store directory

Error Supporting Info System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.IO.IsolatedStorage.IsolatedStorageException: Unable to create the store directory. (Exception from HRESULT: 0x80131468) at System.IO.IsolatedStorage.IsolatedStorageFile.GetRootDir(IsolatedStorageScope scope, StringHandleOnStack retRootDir) at System.IO.IsolatedStorage.IsolatedStorageFile.InitGlobalsNonRoamingUser(IsolatedStorageScope scope) at System.IO.IsolatedStorage.IsolatedStorageFile.GetRootDir(IsolatedStorageScope scope) at System.IO.IsolatedStorage.IsolatedStorageFile.GetGlobalFileIOPerm(IsolatedStorageScope scope) at System.IO.IsolatedStorage.IsolatedStorageFile.Init(IsolatedStorageScope scope) at System.IO.IsolatedStorage.IsolatedStorageFile.GetStore(IsolatedStorageScope scope, Type domainEvidenceType, Type assemblyEvidenceType) at MS.Internal.IO.Packaging.PackagingUtilities.ReliableIsolatedStorageFileFolder.G...
Read more