SQL Server 2008 – order by strings with number numerically

by

You can do it using PATINDEX() function like below :

select * from Test 
order by CAST(SUBSTRING(Name + '0', PATINDEX('%[0-9]%', Name + '0'), LEN(Name + '0')) AS INT)

SQL Fiddle Demo

If you have numbers in middle of the string then you need to create small user defined function to get number from string and sort data based on that number like below :

CREATE FUNCTION dbo.fnGetNumberFromString (@strInput VARCHAR(255)) 
RETURNS VARCHAR(255) 
AS 
BEGIN 
    DECLARE @intNumber int 
    SET @intNumber = PATINDEX('%[^0-9]%', @strInput)

    WHILE @intNumber > 0
    BEGIN 
        SET @strInput = STUFF(@strInput, @intNumber, 1, '')
        SET @intNumber = PATINDEX('%[^0-9]%', @strInput)
    END 

    RETURN ISNULL(@strInput,0) 
END 
GO

You can sort data by :

select Name from Test order by dbo.fnGetNumberFromString(Name), Name

More Related Contents:

  1. SQL Server insert if not exists best practice [closed]
  2. An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server
  3. Are GUID collisions possible?
  4. best way to convert and validate a date string
  5. Why do table names in SQL Server start with “dbo”?
  6. Add alternating row color to SQL Server Reporting services report
  7. LIKE vs CONTAINS on SQL Server
  8. How do I enable MSDTC on SQL Server?
  9. How Do I Split a Delimited String in SQL Server Without Creating a Function?
  10. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  11. Is there any difference between DECIMAL and NUMERIC in SQL Server?
  12. How to enable bulk permission in SQL Server
  13. Should every User Table have a Clustered Index?
  14. How to load an XML file into a database using an SSIS package?
  15. TSQL: Create a view that accesses multiple databases
  16. How to connect to MS SQL Server using Inno Setup?
  17. Check if a temporary table exists and delete if it exists before creating a temporary table
  18. SQL count rows in a table
  19. What is the sql connection string I need to use to access localhost\SQLEXPRESS with Windows Authentication or SQL Authentication?
  20. SQL Error with Order By in Subquery
  21. Executing set of SQL queries using batch file?
  22. How to change default language for SQL Server?
  23. T-SQL incrementing counter based on change in a column value
  24. Removing the remembered login and password list in SQL Server Management Studio
  25. The request failed or the service did not respond in a timely fashion?
  26. Joining tables from different servers
  27. How to find a text inside SQL Server procedures / triggers?
  28. How do you open an SDF file (SQL Server Compact Edition)? [closed]
  29. What is the meaning of “#” in front of a table name in TSQL?
  30. T-SQL for finding Redundant Indexes

Leave a Comment