There are occasions where you would like to search in a varchar column and order the results based on the numeric part of the field.
Imagine the following table:
ABC ABC1 ABC2 ABC3 and so on... ABC11 ABC12 ABC13 and so on.. ABC20 ABC21 ABC22 and so on..
So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.
When you run
select * from table order by my column asc
you get following results:
ABC ABC1 ABC11 ABC12 ABC13 ABC2 ABC20 ABC21 ABC22 ABC3 ABC31 ABC32
In this scenario, you would need the results sorted by the numeric value and not alphabetically.
ABC ABC1 ABC2 ABC3 ABC11 ABC12 ABC13 ABC20 ABC21 ABC22 ABC31 ABC32
How can this be accomplished?
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)
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