donhannam
asked on
SQL - Sort order of string with numbers in
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.
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.
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
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
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)
ASKER
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?.
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?.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks - In the format that is required this works perfectly.
donhannam, replacing the alpha with numeric makes no sense at all, I'd like you to explain why you wanted that.
ASKER
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.
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.
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.
ASKER
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.
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.
Open in new window