SQL Server 2008 - order by strings with number numerically

Question

I have following values in my 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 I do select * from table order by my column asc I get following results:

ABC
ABC1
ABC11
ABC12
ABC13
ABC2
ABC20
ABC21
ABC22
ABC3
ABC31
ABC32

I need it sorted numerically:

ABC
ABC1
ABC2
ABC3
ABC11
ABC12
ABC13
ABC20
ABC21
ABC22
ABC31
ABC32

How can this be accomplished?

And where does AB1 fit versus ABC1? Before or After? - Best bet, I'd say is to maintain a sorting column to avoid sorting on stuff like that.Because you can quickly end up with quite a complex order by which will slow your query way down. — Allan S. Hansen, Nov 27 '13 at 10:53
So `ABC` can actually be any string with any length? Can it also contain numeric characters, e.g. `A1B2C1234`? If so, how do you want to sort that? — Tim Schmelter, Nov 27 '13 at 11:01

Upendra Chaudhari · Answer 1 · 2013-11-28T09:31:39.980

17

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

edited Nov 28 '13 at 09:31

answered Nov 27 '13 at 10:59

Upendra Chaudhari

6,473
5
25
42

Oooh, nice one, +1! Works fine as long as the string part comes first and the number part comes last. – Josien Nov 27 '13 at 11:04
This would work great if all my values were in the format: stringnumber, however I have some values such as AD2_NDU, ECOS_10_CHANGEOFSTATUS, etc. and the solution above errors out for these values...is there something that can be addedd to only apply this format to string that ends with numbers? – user2179092 Nov 28 '13 at 01:02
If there is no. in between string then how should it sort ? i.e. which will come first in these nos ? "AD2_NDU" or "ABC" or "ABC1" or "ABC2" – Upendra Chaudhari Nov 28 '13 at 04:14
well, I get no errors but it is not sorting it correctly... I get values that start with 'B' or 'C' before those that start with 'A'. – user2179092 Nov 28 '13 at 09:00
Results with using the function: – user2179092 Nov 28 '13 at 09:00
BECLAIMS CDB CIW_ASC CIW_BCS CIW_NCSC CLAIMS AGNT_DOC APFORMS ASCBilling AUTH_FORM AD_NDU CMSOPTO CMSPGBA COBMI COBNONMI COVERSHEET CUST_INQ CUST_INQUIRY DANFTPTEST DBILLMBR ENRLBILL ENROLL_UNDER EPIP FAC_ROSTER FECLAIMS FIELD_UNDER FILE_MT FIN_RPT GRIEV_AP MANON837 CIW9 STATUS_90 CMS_9200 STATUS_93 STATUS_95 STATUS_98 CIW99 CIW999 – user2179092 Nov 28 '13 at 09:02
My desired result should be AD_NDU AD2_NDU ADA2006 AGNT_DOC APFORMS ASCBilling AUTH_FORM AUTH_FORM2 BC_U600 BECLAIMS CDB CIW_ASC CIW_BCS CIW_NCSC CIW1 CIW2 CIW3 and so on... – user2179092 Nov 28 '13 at 09:04
You can sort result on name after function value as I have done in my updated answer. Check it – Upendra Chaudhari Nov 28 '13 at 09:32
it is still not sorting correctly =( AD_NDU AGNT_DOC APFORMS ASCBilling AUTH_FORM BECLAIMS CDB CIW_ASC CIW_BCS CIW_NCSC CLAIMS CUST_INQ4 DBILLMBR4 MCAIDUB4 CMS_405 CIW41 CMS_410 CMS_412 CIW48 CIW49 CIW5 CUST_INQ5 DBILLMBR5 CIW50 DENTAL_500 BC_U600 CIW61 CIW62 CIW63 CIW64 CIW65 CIW66 CIW7 CIW8 – user2179092 Nov 28 '13 at 17:11
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/42166/discussion-between-upendra-chaudhari-and-user2179092) – Upendra Chaudhari Nov 29 '13 at 04:39
Still I can't understand what you want actually, if you want to sort data based on nos and then alphabets, you just keep function followed by name in order by clause, if you want to give priority on alphabet first then change the order of columns in order by clause, that's it simple. – Upendra Chaudhari Nov 29 '13 at 06:18
Very nice. A variant, which I am using for sorting identifiers that start with a string of digits and may optionally have suffixed a hypen and digit, or one or more characters, or one or more characters followed by a hyphen and digit (how do I format a comment??) `select * from @work2 order by CAST(SUBSTRING('0'+ unitcode, 0, PATINDEX('%[A-Za-z\-]%', '0'+unitcode)) AS INT), SUBSTRING('0'+ unitcode, PATINDEX('%[A-Za-z\-]%', '0'+ unitcode), LEN('0'+ unitcode))` – Rebeccah Jan 30 '15 at 23:10
OK, a correction after some debugging: `select * from @temptable order by case when patindex('%[A-Za-z\-]%', '0'+unitcode) > 0 then CAST(SUBSTRING('0'+ unitcode, 0, PATINDEX('%[A-Za-z\-]%', '0'+unitcode)) AS INT) else CAST('0' + unitcode AS INT) end, case when patindex('%[A-Za-z\-]%', '0'+unitcode) > 0 then SUBSTRING('0'+ unitcode, PATINDEX('%[A-Za-z\-]%', '0'+ unitcode), LEN('0'+ unitcode)) else '' end` – Rebeccah Jan 30 '15 at 23:59

Marc.2377 · Answer 2 · 2018-12-22T01:45:04.970

16

(based on answers from @shenhengbin and @EchO to this question)

The following is what I call a "clean hack". Assuming you are ordering on column Col1:

ORDER BY LEN(Col1), Col1

It is a hack, although I'd personally feel proud using it.

edited Dec 22 '18 at 01:45

answered Dec 21 '18 at 19:39

Marc.2377

7,807
7
51
95

3

really smart hack - I feel honoured to be able to use it ;-) – Nicolas Feb 09 '21 at 11:25

Shahdat · Answer 3 · 2016-12-28T18:29:40.460

7

In order by statement, prepend enough zeros with when value contains any number in it to make all alphanumerica value same length

SELECT ColName
FROM TableName
ORDER BY 
 CASE WHEN ColName like '%[0-9]%' 
 THEN Replicate('0', 100 - Len(ColName)) + ColName
 ELSE ColName  END

edited Dec 28 '16 at 18:29

answered Dec 28 '16 at 17:45

Shahdat

5,343
4
37
37

score 5 · Answer 4 · answered Nov 27 '13 at 10:54

5

You could remove the first three characters and cast the rest to int

SELECT Value,
       Num=CAST(RIGHT(Value, LEN(Value) - 3) AS int)
FROM dbo.TableName
ORDER BY Num

Demo

answered Nov 27 '13 at 10:54

Tim Schmelter

450,073
74
686
939

3

Nice solution, if the string always has length 3 - wording in the question seems to suggest that's not the case though... – Josien Nov 27 '13 at 10:57
1

It will work only for fix 3 character string, it will not work for string like "ABCD11" – Upendra Chaudhari Nov 27 '13 at 11:08
@UpendraChaudhari: Yes, that's what Josien has already mentioned. I have overlooked the part that the string is arbitrary. – Tim Schmelter Nov 27 '13 at 11:11

score 2 · Answer 5 · edited May 23 '17 at 12:32

2

You could adapt the function RemoveNonAlphaCharacters in this answer to filter out everything except numbers, and then use an ORDER BY using that function.

edited May 23 '17 at 12:32

Community

1
1

answered Nov 27 '13 at 10:55

Josien

13,079
5
36
53

score 1 · Answer 6 · answered Feb 08 '21 at 23:39

Using Standard SQL ONLY this query demonstrates how you can ODER BY either the numbers found to be contained at the Beginning or the End of the string. The query also shows how you could then look at the remaining 'inner' portion of the string to see if there are any numbers contained there. Knowing if there are numbers contained within the string could be useful if further processing is necessary.

WITH stringNumberData AS
(   /* Build up Fake data with Numbers at the Beginning, End and Middle of the string */
    SELECT 1  AS uniqueKey, 'ABC'      AS NumberFromString UNION ALL
    SELECT 2  AS uniqueKey, 'ABC1'     AS NumberFromString UNION ALL
    SELECT 3  AS uniqueKey, 'ABC2'     AS NumberFromString UNION ALL
    SELECT 4  AS uniqueKey, 'ABC3'     AS NumberFromString UNION ALL
    SELECT 5  AS uniqueKey, 'ABC10'    AS NumberFromString UNION ALL
    SELECT 6  AS uniqueKey, 'ABC11'    AS NumberFromString UNION ALL
    SELECT 7  AS uniqueKey, 'ABC12'    AS NumberFromString UNION ALL
    SELECT 8  AS uniqueKey, 'ABC20'    AS NumberFromString UNION ALL
    SELECT 9  AS uniqueKey, 'ABC21'    AS NumberFromString UNION ALL
    SELECT 10 AS uniqueKey, 'ABC22'    AS NumberFromString UNION ALL
    SELECT 11 AS uniqueKey, 'ABC30'    AS NumberFromString UNION ALL
    SELECT 12 AS uniqueKey, 'ABC31'    AS NumberFromString UNION ALL
    SELECT 13 AS uniqueKey, 'ABC32'    AS NumberFromString UNION ALL
    SELECT 14 AS uniqueKey, '1ABC'     AS NumberFromString UNION ALL
    SELECT 15 AS uniqueKey, '2ABC'     AS NumberFromString UNION ALL
    SELECT 16 AS uniqueKey, '3ABC'     AS NumberFromString UNION ALL
    SELECT 17 AS uniqueKey, '10ABC'    AS NumberFromString UNION ALL
    SELECT 18 AS uniqueKey, '11BC'     AS NumberFromString UNION ALL
    SELECT 19 AS uniqueKey, '12ABC'    AS NumberFromString UNION ALL
    SELECT 20 AS uniqueKey, '10ABC18'  AS NumberFromString UNION ALL
    SELECT 21 AS uniqueKey, '11BC52'   AS NumberFromString UNION ALL
    SELECT 22 AS uniqueKey, '12ABC42'  AS NumberFromString UNION ALL
    SELECT 23 AS uniqueKey, 'A3BC18'   AS NumberFromString UNION ALL
    SELECT 24 AS uniqueKey, 'B3C52'    AS NumberFromString UNION ALL
    SELECT 25 AS uniqueKey, '12AB3C'   AS NumberFromString UNION ALL
    SELECT 26 AS uniqueKey, 'A3BC'     AS NumberFromString UNION ALL
    SELECT 27 AS uniqueKey, 'AB2C'     AS NumberFromString UNION ALL
    SELECT 28 AS uniqueKey, 'ABC85D'   AS NumberFromString

)   

SELECT d.uniqueKey, d.NumberFromString
    /* Extract numerical values contained on the LEFT of the String by finding the index of the first non number */
    , LEFT(d.NumberFromString, PATINDEX('%[^0-9]%', d.NumberFromString) -1) AS 'Left Numbers Extraction'

    /* Extract numerical data contained on the RIGHT of the String */
    , RIGHT(d.NumberFromString, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) - 1 ) AS 'Right Numbers Extraction'

    /* The below checks inside the Inner string to determine if numbers exists within it.  Could be used for further processing if further extraction is necessary */
    , PATINDEX('%[0-9]%',
        SUBSTRING(d.NumberFromString /*, Start Pos, Length to Extract) */
            , PATINDEX('%[^0-9]%', d.NumberFromString)  /* Start Pos is first left non number */
            /* The below obtains the length of the Inner String so it can be extracted */
            , LEN(d.NumberFromString) - ((PATINDEX('%[^0-9]%', d.NumberFromString) -1 )) -  (PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1) /* (String Length) - (LEFT Numbers) - (RIGHT Numbers) */
      )) AS innerNumExists

    /* The two lines below tell us if there exists a number at the Beginning and/or End of the string */
    , PATINDEX('%[0-9]%',  LEFT(d.NumberFromString, 1)) AS leftNumExists
    , PATINDEX('%[0-9]%', RIGHT(d.NumberFromString, 1)) AS rightNumExists

    /* Locates and returns the index of the very first number located in the string from Left to Right */
    , PATINDEX('%[^0-9]%', d.NumberFromString) AS firstLeftNonNum_index

    /* Locates and returns the index of the very first number located in the string from Right to Left */
    , LEN(d.NumberFromString) - (PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1 ) AS firstRightNonNum_index

    /* Get the length of the numbers existing from Right to Left up to the first non numeric character */   
    , PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1 AS rightStringLen


FROM stringNumberData d
ORDER BY 
    /* Ordering first by numbers found on the LEFT of the string */
    CAST(LEFT(d.NumberFromString, PATINDEX('%[^0-9]%', d.NumberFromString) -1) AS INT )
    /* Ordering second by numbers found on the RIGHT of the string */
    , CAST(RIGHT(d.NumberFromString, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) - 1 ) AS INT )
;

critical_error · Answer 7 · 2021-02-09T00:10:23.827

Here's a simple to understand example for those using SQL Server 17+.

DECLARE @Data table ( val varchar(10) );
INSERT INTO @Data VALUES
    ( 'ABC' ),( 'ABC1' ),( 'ABC11' ),( 'ABC12' ),( 'ABC13' ),( 'ABC2' ), ( 'B1C' ),
    ( 'ABC20' ),( 'ABC21' ),( 'ABC22' ),( 'ABC3' ),( 'ABC31' ),( 'ABC32' );

SELECT val FROM @Data AS d
CROSS APPLY (
    SELECT CAST ( TRANSLATE ( d.val, 'abcdefghijklmnopqrstuvwxyz', '                          ' ) AS int ) AS Num
) AS x
ORDER BY
    LEFT ( val, 1 ), Num;

Returns

+-------+
|  val  |
+-------+
| ABC   |
| ABC1  |
| ABC2  |
| ABC3  |
| ABC11 |
| ABC12 |
| ABC13 |
| ABC20 |
| ABC21 |
| ABC22 |
| ABC31 |
| ABC32 |
| B1C   |
+-------+

SQL Server's TRANSLATE takes three parameters: inputString, characters, translations.

The inputString in your case is your column name.

The characters are the values you're looking to replace, in this case the alphabet.

The translations are the values to replace with. This string must be equal in length to the characters--hence the empty string that's 26 spaces long.

And finally, using CAST ignores the spaces and allows the remaining value to be sorted as an int.

You can read about TRANSLATE here:

https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver15

SQL Server 2008 - order by strings with number numerically

7 Answers7

Linked

Related