Alphanumeric Sort

Question

I need quick help in sorting data from SQL side. I am using Sqlserver 2012 (well, if you give an answer with a new feature).

I am already looking at some links like Sort in alphanumeric format , Alphanumeric string Sort in the Sqlserver - Code project . But it does not give the desired result.

Another thing I tried:

 CREATE TABLE dbo.Section ( Section varchar(50) NULL ) INSERT INTO dbo.Section (Section.Section) VALUES ('Campsit no.43') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsit no.41') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 11') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 1') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 12') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 2') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 3') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 4') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 40') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 41') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite no.20') INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite no.41') INSERT INTO dbo.Section (Section.Section) VALUES ('Cabin') INSERT INTO dbo.Section (Section.Section) VALUES ('Group Tent Campsite') INSERT INTO dbo.Section (Section.Section) VALUES ('Tent Campsite') INSERT INTO dbo.Section (Section.Section) VALUES ('test1') INSERT INTO dbo.Section (Section.Section) VALUES ('test2') INSERT INTO dbo.Section (Section.Section) VALUES ('test11') SELECT Section FROM dbo.Section --Show normal Sort SELECT Section FROM dbo.Section ORDER BY Section --Show AlphaNumberic Sort SELECT Section FROM dbo.Section ORDER BY LEFT(Section,PATINDEX('%[0-9]%',Section)), -- alphabetical sort CONVERT(varchar(50),SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))) -- numerical sort --cleanup our work --DROP Table dbo.Section 

Now I want: if the same line finds in the alphabetical part sorting by first and then digital (consider the space if possible, or you can give the result without a place, for example, camping No. 41 and camping No. 41 will give in same order)

 Actual Result Expected Result Campsit no.41 Campsit no.41 Campsit no.43 Campsit no.43 Campsite No. 1 Campsite No. 1 Campsite No. 11 Campsite No. 2 Campsite No. 12 Campsite No. 3 Campsite No. 2 Campsite No. 4 Campsite No. 21 Campsite No. 11 Campsite No. 3 Campsite No. 12 Campsite No. 4 Campsite No. 21 Campsite No. 40 Campsite No. 40 Campsite No. 41 Campsite No. 41 Campsite no.20 Campsite no.20 --this will good to come here, if possible or if not, then remove space and set approriate Campsite no.41 Campsite no.41 --this will good to come here, if possible or if not, then remove space and set approriate Group Tent Campsite Group Tent Campsite Tent Campsite Tent Campsite test1 test1 test11 test2 test2 test11 

Answer 1

Here's a tip: whenever you have problems sorting, add order by article to the select clause. this will let you see if what you are really sorting is what you want to sort by:

 SELECT Section, CASE WHEN PATINDEX('%[0-9]%',Section) > 1 THEN LEFT(Section,PATINDEX('%[0-9]%',Section)-1) ELSE Section END As alphabetical_sort, -- alphabetical sort CASE WHEN PATINDEX('%[0-9]%',Section) > 1 THEN CAST(SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section)) as float) ELSE NULL END As Numeric_Sort FROM dbo.Section ORDER BY alphabetical_sort, Numeric_Sort 

After I got the correct view, all I had to do was translate the case statements to the order by position:

 SELECT Section FROM dbo.Section ORDER BY CASE WHEN PATINDEX('%[0-9]%',Section) > 1 THEN LEFT(Section,PATINDEX('%[0-9]%',Section)-1) ELSE Section END , -- Alphabetical sort CASE WHEN PATINDEX('%[0-9]%',Section) > 1 THEN CAST(SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section)) as float) ELSE NULL END -- Numeric sort 

Basically you had 4 main problems:

• Your expression in alphabetical order assumes that each line has numbers in it.
• Your expression in alphabetical order contains numbers as well as text.
• Your numerical sort expression has both numerical and alphabetical values.
• Due to article 3, you cannot attribute your numerical sort expression to a numerical type, and that is why you will get a sort string.

See this sql script

Answer 2

Below is the result that you are after, but I doubt that this is proof of a fool. I think you are going to fight for a solution that is infallible and still works well.

The first part is to get the first number (where this happens after the space, so Campsite no.41 handled differently with Campsite no. 40 ), I put this in APPLY to make it easy to reuse the result.

The next step is to search for the first non-numeric character after the first number, that is, where the number ends, so that we can extract the full number using a substring, and then finally use TRY_CONVERT(INT to get this fragment in the sortable type.

 SELECT s.Section, TextPart = SUBSTRING(s.Section, 1, ISNULL(fn.FirstNumber, LEN(s.Section))), Number = CASE WHEN FirstNumber IS NULL THEN NULL ELSE TRY_CONVERT(INT, SUBSTRING(s.section, fn.FirstNumber + 1, ISNULL(ln.LastNumber, LEN(s.Section)))) END FROM dbo.Section AS s -- GET FIRST NUMBER (WHERE PRECEDING CHARACTER IS A SPACE CROSS APPLY (SELECT NULLIF(PATINDEX('% [0-9]%', s.section), 0)) AS fn (FirstNumber) -- GET FIRST NON NUMERIC CHARACTER AFTER FIRST NUMBER CROSS APPLY (SELECT NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(s.section, fn.FirstNumber + 1, LEN(s.Section))), 0)) AS ln (LastNumber) ORDER BY TextPart, Number; 

nb You will need to move the expressions in select in order and not in the alias of the column, but I left it in this format to make it more clear what is happening

I tried to comment on the solution, but there is an honest cue ball, so a full explanation of each bit will be quite complicated. Sorry if something is unclear

---

EDIT

Sorry, I missed the update, where you switched from desire (test1, test11, test2) to (test1, test2, test11). It just changes your logic to look for the first letter, but now it's the one where the previous character is not complete ( PATINDEX('%[^.][0-9]%', s.section) ), not where the previous character was still space (this ensures that Campsite no.20 sorted after Campsite no. 40 )

 SELECT s.Section, TextPart = SUBSTRING(s.Section, 1, ISNULL(fn.FirstNumber, LEN(s.Section))), Number = CASE WHEN FirstNumber IS NULL THEN NULL ELSE TRY_CONVERT(INT, SUBSTRING(s.section, fn.FirstNumber + 1, ISNULL(ln.LastNumber, LEN(s.Section)))) END FROM #Section AS s -- GET FIRST NUMBER (WHERE PRECEDING CHARACTER IS NOT A FULL STOP CROSS APPLY (SELECT NULLIF(PATINDEX('%[^.][0-9]%', s.section), 0)) AS fn (FirstNumber) -- GET FIRST NON NUMERIC CHARACTER AFTER FIRST NUMBER CROSS APPLY (SELECT NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(s.section, fn.FirstNumber + 1, LEN(s.Section))), 0)) AS ln (LastNumber) ORDER BY TextPart, Number; 

Answer 3

Try this here. Note. The cab is in your data but not the expected results. Also, if you want something to change with spaces, just let me know.

 SELECT Section, FormatSection FROM dbo.Section CROSS APPLY (SELECT CASE WHEN PATINDEX('%[0-9]%',Section) != 0 THEN SUBSTRING(Section,0,PATINDEX('%[0-9]%',Section)) + FORMAT(CAST(SUBSTRING(Section,PATINDEX('%[0-9]%',Section),5) AS INT),'0#') ELSE Section END ) AS CA(FormatSection) ORDER BY FormatSection 

Results:

 Section FormatSection -------------------------------------------------- --------------------- Cabin Cabin Campsit no.41 Campsit no.41 Campsit no.43 Campsit no.43 Campsite No. 1 Campsite No. 01 Campsite No. 2 Campsite No. 02 Campsite No. 3 Campsite No. 03 Campsite No. 4 Campsite No. 04 Campsite No. 11 Campsite No. 11 Campsite No. 12 Campsite No. 12 Campsite No. 40 Campsite No. 40 Campsite No. 41 Campsite No. 41 Campsite no.20 Campsite no.20 Campsite no.41 Campsite no.41 Group Tent Campsite Group Tent Campsite Tent Campsite Tent Campsite test1 test01 test2 test02 test11 test11 

Answer 4

I found the following alternative.

Create this function and simply use this function in your query "ORDER BY fnGetNumericFromString ([columnNm])".

 CREATE FUNCTION fnGetNumericFromString (@InString VARCHAR(20), @OutStrType VARCHAR(3)) RETURNS VarChar(20) AS BEGIN -- declare variables DECLARE @pos INT DECLARE @strLength INT DECLARE @NumericString VarChar(20) DECLARE @CharString VarChar(20) DECLARE @ReturnValue VarChar(20) -- set values SET @NumericString = '' SET @CharString = '' SET @pos= 1 SET @strLength = LEN(@InString) SET @InString = UPPER(@InString) SET @OutStrType = UPPER(@OutStrType) --start looping WHILE @pos <= @strLength BEGIN -- number codes are 48 to 57 IF ASCII(SUBSTRING(@InString, @pos, 1))BETWEEN 48 AND 57 SET @NumericString = @NumericString + SUBSTRING(@InString, @pos, 1) else SET @CharString = @CharString + SUBSTRING(@InString, @pos, 1) --increment to next character SET @pos = @pos + 1 END IF @OutStrType = 'STR' SET @ReturnValue = @CharString ELSE SET @ReturnValue = @NumericString RETURN @ReturnValue END select section from Section order by dbo.fnGetNumericFromString(section, 'str'), CAST(dbo.fnGetNumericFromString(section, 'int') AS INT) 

Answer 5

  SELECT *, ROW_NUMBER()OVER(ORDER BY CASE WHEN ISNUMERIC (ID)=1 THEN CONVERT(NUMERIC(20,2),SUBSTRING(Id, PATINDEX('%[0-9]%', Id), LEN(Id)))END DESC)Rn ---- numerical FROM ( SELECT '1'Id UNION ALL SELECT '25.20' Id UNION ALL SELECT 'A115' Id UNION ALL SELECT '2541' Id UNION ALL SELECT '571.50' Id UNION ALL SELECT '67' Id UNION ALL SELECT 'B48' Id UNION ALL SELECT '500' Id UNION ALL SELECT '147.54' Id UNION ALL SELECT 'A-100' Id )A ORDER BY CASE WHEN ISNUMERIC (ID)=0 /* alphabetical sort */ THEN CASE WHEN PATINDEX('%[0-9]%', Id)=0 THEN LEFT(Id,PATINDEX('%[0-9]%',Id)) ELSE LEFT(Id,PATINDEX('%[0-9]%',Id)-1) END END DESC