alphanumeric sort on inconsistant values
-
I am running SQL Server 2000 and need to perform a sort on an alphanumeric field that does not have a consistent pattern for the values contained in the column to be sorted. For example, the column name is FILENUM varchar(30). Given a set of filenumbers:
1dd
1
1x4
1cc2
1110-345-720a3
11
380-41-3a
10
should be sorted as:
1
1cc2
1dd
1x4
10
11
380-41-3a
1110-345-720a3
I am having difficulty finding a solution to sort the data as requested. Any assistance would be greatly appreciated.
thanks,
Tracy
-
This would allow you to sort based on each character.
order by substring(filenum,1,1), substring(filenum,2,1)...
What is the logic behind having 380-41-3a appear before 1110-345-720a3
and 1x4 before 10?
Could you strip out all the letters and symbols and then sort by the resulting number?
Michelle -
see if this link helps any...there aren't any alphas afaik but the logic seems to be what you're looking for..
**ASCII stupid question, get a stupid ANSI !!!** -
Michelle-
The logic behind having 380-41-3a appear before 1110-345-720a3 is that the integer 380 comes before 1110. I cannot strip out the letters as they would want them to be used for sorting also, for example 380-41-3a would come before 380-41-3b. Their logic for having 1x4 before 10 is that the integer 1 comes before 10. They basically want me to sort the numerics in the filenumber as an Integer and the letters as alpha. It seems I would have to break each down into a substring and sort based upon that. However, not all filenumbers contain a letter and the filenumbers vary in length. Hope this makes sense. I'm not even sure if what they are asking is doable.
thanks,
Tracy
-
Someone with some smarts might be a better help. I can't come up with a way to do what they are asking.
Michelle -
This is not dealing with the Alpha characters well, (I threw this together), but it might help someone smarter than I to get their brains working:
CREATE TABLE #Sort( [Value] varchar(100))
INSERT INTO #Sort VALUES( '1dd')
INSERT INTO #Sort VALUES( '1')
INSERT INTO #Sort VALUES( '1x4')
INSERT INTO #Sort VALUES( '1cc2')
INSERT INTO #Sort VALUES( '1110-345-720a3')
INSERT INTO #Sort VALUES( '11')
INSERT INTO #Sort VALUES( '380-41-3a')
INSERT INTO #Sort VALUES( '10')
SELECT *
FROM #Sort
ORDER BY CASE
WHEN ASCII( SUBSTRING( [Value], CHARINDEX( '1234567890', [Value], 1),
(CHARINDEX( '-', [Value], 1) ))) BETWEEN CHAR(48) AND CHAR(57)
THEN CONVERT( integer, SUBSTRING( [Value], CHARINDEX( '1234567890', [Value], 1),
(CHARINDEX( '-', [Value], 1) )))
ELSE SUBSTRING( [Value], CHARINDEX( '1234567890', [Value], 1), (CHARINDEX( '-', [Value], 1) ))
END
DROP TABLE #Sort
[Note: it is the second part of the substring I am having difficulty, (trying to figure out how to include numerous choices for the CHARINDEX or trying to choose anything that is not a number...]
I wasn't born stupid - I had to study.
-
This is a working sol IF you don't mind the speed...
The idea is to padd all numbers from your strings to a certain len ( a max len that should cover all lengths of your numbers I used 10 in this case)
----- START FUNCTION-----
CREATE FUNCTION [dbo].[nmbPadd]
(@mystr varchar(20))
RETURNS varchar(4000)
AS
BEGIN
declare @StartNmb int
declare @StopNmb int
declare @pos int
declare @isNumb bit
declare @myLen int
declare @rez varchar(4000)
if (@myStr is null or len(@myStr)=0) return ''
set @rez=''
set @myLen=len(@mystr)
set @isNumb=0
set @pos=1
while (@pos<=@myLen)
begin
if (charindex(substring(@mystr,@pos,1),'0123456789')>0)
begin
--check to see if the prev char was numeric
if (@isNumb=1)
begin
set @StopNmb=@StopNmb+1
end
else
begin--prev char was NOT numeric
set @StartNmb=@pos
set @StopNmb=1
end
set @isNumb=1
end
else--our current char is not numeric
begin
--check to see if the prev char was numeric
if (@isNumb=1)
begin
--10 is max number of digitis that a number can have
set @rez=@rez+replicate('0',10-@StopNmb)+substring(@myStr,@StartNmb,@StopNmb+1)
end
else
begin--prev char was NOT numeric
set @rez=@rez+substring(@mystr,@pos,1)
end
set @isNumb=0
end
set @pos=@pos+1
end
if (@isNumb=1)
set @rez=@rez+replicate('0',10-@StopNmb)+substring(@myStr,@StartNmb,@StopNmb)
return @rez
END
---- END FUNCTION------
GO
declare @t table (sort varchar(20))
insert into @t
select '1dd' union all
select '1' union all
select '1x4' union all
select '1cc2' union all
select '1110-345-720a3' union all
select '11' union all
select '380-41-3a' union all
select '10'
select sort from @t
order by [dbo].[nmbPadd](sort)
Kindest Regards,Vasc
-
I get an error: Server: Msg 156, Level 15, State 1, Procedure nmbPadd, Line 72
Incorrect syntax near the keyword 'declare'.
I wasn't born stupid - I had to study.
-
Strange ... the function has only 65 lines ... Did you copy paste correct?
Kindest Regards,Vasc
-
The GO was missing. Did you just add that along with the comments?
Thanks (great solution...). I was hoping to be able to do this without having to loop through all of the values...
I wasn't born stupid - I had to study.
-
ya I added the GO
But I didn't inted the first time to post a QA batch to create the function and run the query...
Like I said it is UGLY but is working...
Dunno how to do a clean solution for this ... maybe another aproach
Kindest Regards,Vasc
-
A big THANK YOU to Vasc!
This solution works pefectly for us.
thanks!
Tracy
-
Vasc,
DECLARE @sort TABLE ( [Value] varchar(100))
INSERT INTO @sort VALUES( '1dd')
INSERT INTO @sort VALUES( '1')
INSERT INTO @sort VALUES( '1x4')
INSERT INTO @sort VALUES( '1cc2')
INSERT INTO @sort VALUES( '1110-345-720a3')
INSERT INTO @sort VALUES( '11')
INSERT INTO @sort VALUES( '380-41-3a')
INSERT INTO @sort VALUES( '10')
SELECT
Value
FROM @sort
ORDER BY
CONVERT(INT, CASE
WHEN ISNUMERIC(VALUE)= 1 THEN Value
ELSE LEFT(Value, PATINDEX('%[^1234567890]%',Value)-1) END),
PATINDEX('%[abcdefghijklmnopqrstuvwxyz-]%',Value)-1 ASC,
Value ASC
Just as a FYI the ^ means NOT - so it is looking for any non-numeric value.
Hope this gives someone another choice sometime down the road.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply