Solved

SQL - Sort order of string with numbers in

Posted on 2011-03-03
10
1,081 Views
Last Modified: 2012-05-11
I have a table with a list of parts with codes like:-

9f99
9f100
9f101
...
9f1001

etc.

I want to be able to sort these in the order above - a string field will sort the 100 before the 99.

It is possible that the format of these code may change i.e. could be 2 alpha characters in the code.

I thought there may be a way to convert the letters to a 2 digit number 01 - 26 and replace them so I get a number to sort on. In this way all similar codes would be in the right order?

Appreciate any ideas on this.
0
Comment
Question by:donhannam
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35032883
If it always start with 2 alpha characters, you can strip off those 2 character, convert to Int and sort.
select code
  from your_table 
 order by CONVERT(int,SUBSTRING(code,3,LEN(code)))

Open in new window

0
 
LVL 6

Expert Comment

by:judgeking
ID: 35033019
You can do it by creating a function in SQL server that returns the numeric part, then sorts by that.  If you create the GetCodeValue function below, then you could sort your data like this:

SELECT  * FROM Parts ORDER BY dbo.GetCodeValue(code), code
CREATE FUNCTION [dbo].[GetCodeValue](@Code nvarchar(100))
RETURNS int
AS
BEGIN
    DECLARE @iCounter int
    DECLARE @iLen int
    DECLARE @iFound int
    DECLARE @iCodeValue int
    DECLARE @sChar nvarchar(1)

    SET @iLen = LEN(@Code)
    SET @iFound = 0
    SET @iCodeValue = 0

    IF @iLen > 0
    BEGIN
      SET @iCounter = @iLen
      WHILE @iFound = 0 AND @iCounter >= 1
      BEGIN
        SET @sChar = SUBSTRING(@Code, @iCounter, 1)

        IF ISNUMERIC(@sChar) = 1
          SET @iCounter = @iCounter - 1
        ELSE
          SET @iFound = 1
      END

      IF @iFound = 1
      BEGIN
        SET @iCodeValue = CAST(SUBSTRING(@Code, @iCounter + 1, @iLen - @iCounter) AS int)
      END
    END

    RETURN @iCodeValue
END

Open in new window

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35033287
You can sort by the first 2 alphanumeric characters first, then sort the reset as numbers.
SELECT *
FROM tablename
ORDER BY LEFT(fieldname,2), CAST(SUBSTRING(fieldname,3,1000) as int)

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:donhannam
ID: 35033371
Thanks for comments - issue is I need to sort by numeric then alpha then numeric and carn't be sure of exact number of characters between each.

judgeking: I think your solution is the best using a function - at present it strips off all characters before and including the last alpha character.

This would work if when it found a character it could replace with a 2 digit number.

I think I can work out the code to do this now and guess I could go case A = '01', B = '02' etc but is there a function to convert characters into there repespective numbers?.
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 35033439
I don't see how replacing a character with a 2-digit number would solve the issue but here's a query which would do that
DECLARE @t table (code nchar(10))

INSERT @t (code)
SELECT '9f99'
UNION ALL SELECT '9f100'
UNION ALL SELECT '9f101'
UNION ALL SELECT '2vs250'
UNION ALL SELECT 'cx120'
UNION ALL SELECT '1as205'

;WITH CTE AS(
  SELECT REPLACE(code,'A','01') newcode, code ,1 n FROM @t
  UNION ALL
  SELECT REPLACE(newcode, CHAR(65 + n) , RIGHT('0' + CAST(n+1 as varchar),2)) newcode, code ,n+1 FROM CTE
  WHERE n < 26
)
SELECT newcode,code
FROM CTE
WHERE n=26
/*
newcode     code
10119205    1as205
107120      1g120
22219250    2vs250
905100      9e100
90699       9f99
*/

Open in new window

0
 

Author Closing Comment

by:donhannam
ID: 35033474
Thanks - In the format that is required this works perfectly.
0
 
LVL 6

Expert Comment

by:judgeking
ID: 35033762
donhannam, replacing the alpha with numeric makes no sense at all, I'd like you to explain why you wanted that.
0
 

Author Comment

by:donhannam
ID: 35041706
Judgeking

In this case all items that need to be grouped together are in the same format i.e. same number of digits. When we replace the alpha with numeric ends up in the right order i.e 9f99 is before 9f100 and 34ft24 is before 34ft120. I use an additional sort to get the parts in groups.
0
 
LVL 6

Expert Comment

by:judgeking
ID: 35085925
I guess you don't know much about SQL, no need to convert the alpha chars, SQL can sort by either or both.  My code and select statement above would have done exactly what you want in a more elegant and flexible way.  I think I deserved at least an assist on that, I spent quite a bit of time on it.  I don't answer questions just for fun, I need to get a certain amount per month or lose my membership.
0
 

Author Comment

by:donhannam
ID: 35087136
judgeking:

Appoligies - I though you did both posts and allocated points to last one.

I probably would have allocated points between both if I had realised but I don't think I can go back and change now and actually the result I gave points to was the one that worked for me - note it did require conversion of the alpha characters to work properly.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlServer no dupes 25 34
Querying data from 3 SQL tables 2 32
create an aggregate function 9 34
SQL Function NOT ROUND 9 9
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question