h1

SQL Alpha-Numeric Column Sorting

May 12, 2012

First create a function as folloing.

CREATE FUNCTION fn_AlphaNumSorting
(
@input varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
declare @num varchar(50)
declare @space varchar(50)
declare @index int
set @index = 1set @num = LEFT(SUBSTRING(@input, PATINDEX(‘%[0-9.-]%’, @input), 8000), PATINDEX(‘%[^0-9.-]%’, SUBSTRING(@input, PATINDEX(‘%[0-9.-]%’, @input), 8000) + ‘X’)-1)
set @space = replicate(‘ ‘, 20 – len(@num))return replace(@input, @num, @space + @num)
END

Now you can use this like

select * from <tableName> order by dbo.fn_AlphaNumSorting(<columnName>)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: