sayedkhalid99 (1/3/2014)
what change shall i bring in here so that if the serial don't have any decimal point say , it gives error in left function.DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))
INSERT INTO @Result
SELECT '1.1','a' UNION ALL
SELECT '1.2.1','b' UNION ALL
SELECT '1.2.2','C' UNION ALL
SELECT '1.2.3.4','G' UNION ALL
SELECT '1.11','B' UNION ALL
SELECT '1.2.33.4.5','Extra row' UNION ALL
SELECT '2.3','B' UNION ALL
SELECT '2.11','B' UNION ALL
SELECT '2.2','C' UNION ALL
SELECT '1.5','E' UNION ALL
SELECT '1','E'
With absolutely no disrespect intended or implied towards anyones fine work on this thread, my recommendation would be to not do it in any way that has such a small limit on the number of levels because there will be a drop-everything panic if a 6th or 7th level ever shows up. Don't say it won't happen. Make your code bullet proof and scalable and just assume that it will happen when you can least afford it to.
As a bit of a sidebar, if it were me, I'd stop recaclulating things that won't change over and over again. I'd turn my code into a function and add a persisted computed column that contains the function to the table so that I don't ever have to worry about calculating a sort order on something that's almost perfectly static.
As a hidden benefit of using the code I posted, you'll also have the DelimitedSplit8K function, which you find dozens of other uses for.
--Jeff Moden
Change is inevitable... Change for the better is not.