January 14, 2011 at 4:39 pm
[Edited my original]
Hi all. I am running into a sorting issue and haven't found a solid way to handle. Here is the script you can use to see my issue. First time posting an issue, so hopefully the format keeps.
CREATE TABLE #Testing (filenames varchar(100));
SET NOCOUNT ON;
INSERT INTO #Testing VALUES ('EveningStar, Chris A._1.pdf');
INSERT INTO #Testing VALUES ('EveningStar, Chris A._10.pdf');
INSERT INTO #Testing VALUES ('EveningStar, Chris A._11.pdf');
INSERT INTO #Testing VALUES ('EveningStar, Chris A._12.pdf');
INSERT INTO #Testing VALUES ('EveningStar, Chris A._13.pdf');
INSERT INTO #Testing VALUES ('EveningStar, Chris A._2.pdf');
INSERT INTO #Testing VALUES ('EveningStar, Chris A._3.pdf');
INSERT INTO #Testing VALUES ('EveningStar, Chris.pdf');
INSERT INTO #Testing VALUES ('112010.pdf');
INSERT INTO #Testing VALUES ('112010_1.pdf');
SELECT filenames, REPLACE(SUBSTRING(filenames, CHARINDEX('_', filenames) + 1, LEN(filenames)),'.pdf', '') as sorting_my_stuff
FROM #Testing
ORDER BY filenames;
SELECT filenames, REPLACE(SUBSTRING(filenames, CHARINDEX('_', filenames) + 1, LEN(filenames)),'.pdf', '') as sorting_my_stuff
FROM #Testing
ORDER BY sorting_my_stuff;
Expected Outcoming Sort order is
Eveningstar, Chris.pdf
EveningStar, Chris A._1.pdf
EveningStar, Chris A._2.pdf
EveningStar, Chris A._3.pdf
EveningStar, Chris A._10.pdf
EveningStar, Chris A._11.pdf
EveningStar, Chris A._12.pdf
EveningStar, Chris A._13.pdf
112010.pdf
112010_1.pdf
Any help would be greatly appriciated.
January 14, 2011 at 7:48 pm
I hope I've inferred your requirements properly from the example data, but you may have to do some more work if not. It looks as though you want to treat the content of "filenames" as two separate elements, the base name and an optional version number. Your first-cut code does a good job of isolating the version number (as "sorting_my_stuff"), but you also have to isolate the base document name as well. One more thing to make the sort work as you intended is to convert the version number to an integer so you get a numeric sort. Finally, we can use CASE statements to handle the exception cases with no version number. Once that's done, you may simply sort on the two now-separated parts of the key.
I reformatted the code a bit for my own benefit in being able to more easily follow the nested functions -- charindex inside substring inside replace. One last thing that I didn't attack was the implied requirement to have numeric base names sort before alpha names. You can probably do that if it's really needed by use of a third part to the sort key, again building it with a case statement depending on whether the first character is numeric, or whatever works for you.
Thanks for posting this with code to create test data and your preliminary queries. I enjoyed the challenge.
SELECT filenames
,Case When CHARINDEX('_',filenames) = 0
then REPLACE(filenames
,'.pdf'
, ''
)
else Left(filenames
,CHARINDEX('_',filenames)
)
end
as SortKeyPart1
,Case When CHARINDEX('_',filenames) = 0 then convert(int,0)
else CONVERT(INT,
REPLACE(SUBSTRING(filenames
,CHARINDEX('_',filenames)+ 1
,LEN(filenames))
,'.pdf'
, ''
)
)
end
as SortKeyPart2
FROM #Testing
ORDER BY SortKeyPart1, SortKeyPart2
January 14, 2011 at 7:58 pm
Wow! Thanks John. I did not even think about isolating it that way. So far it looks like it does what is expected. Great Job! I am going to run some more tests through it and make sure the expected outcomes are matching. Again thanks and I'll be sure to follow-up after testing more.
January 15, 2011 at 3:51 pm
Hey John, just finished up testing it. I had to make a couple of minor changes. Based on various filenames and some having 2 or more "_", I ended up using your idea of two sort columns but with a minor twist. One, returning just alpha characters, and the other returning the numeric values. This seems to be doing the trick based on all the scenerio's I've pulled and ran it against. Had it not been for your solution, I'd probably still be stuck right now. Thanks again and I really appriciate the help! ! !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply