sorting a column
-
Hi,
Was just curious that how parsename will work if the number of '.' are more than 3.
I feel this is being used to represent some sort of tree data.
Jeswanth
--------------------------------
-
You may have a point there
And even though it may not be useful to the original problem, I think the following is an interesting problem:
Write a select statement that transforms
'1.2.3.4.5' to 1 2 3 4 5
'11.22.33.44.55' to 11 22 33 44 55
'1.2.33' to 1 2 33 NULL NULL
and so on.... So far, I haven't been successful
-
And even though it may not be useful to the original problem, I think the following is an interesting problem:
Write a select statement that transforms
Use a function
CREATE FUNCTION dbo.udf_sortkey (@key varchar(255))
RETURNS varchar(255)
AS
BEGIN
DECLARE @input varchar(255),@output varchar(255),@ptr int
SET @input = @key
SET @output = ''
SET @ptr = CHARINDEX('.',@input)
WHILE @ptr > 0
BEGIN
SET @output = @output + RIGHT('0000'+LEFT(@input,@ptr-1),4)
SET @input = SUBSTRING(@input,@ptr+1,255)
SET @ptr = CHARINDEX('.',@input)
END
SET @output = @output + RIGHT('0000'+@input,4)
RETURN @output
END
SELECT * FROM
ORDER BY dbo.udf_sortkey([column])
Far away is close at hand in the images of elsewhere.
Anon. -
You could do that of couse...
But I was hoping for a solution along the lines of Noel's solution - ordering by something which doesn't use a user defined function - as I am a bit worried about performance... On the other hand, using a function with a loop is probably the only possibility if there can be an indefinite number of dots. How about the case of a limited maximal number of dots - e.g 4?It would be great to have a built-in SQL Server function with parameters (@Str1 varchar, @Str2 varchar, @Occ int) that returns what is in between occurrencies @Occ and @Occ + 1 of @Str1 in @Str2 (such that e.g func('.', '12.34.56.78.9', 3) returns '78'). But I don't think it exists
-
Yeah what if my value goes beyond 3 .'s. parsename doesnt work there. any other way to get this resolved?
-
Yes a built in function would be nice, I bet it would use the equivalent of CHARINDEX & SUBSTRING as PARSENAME probably does
Performance is always going to be bad due the amount of string slicing required.
If I had to do this I would create temp table with an additional sortkey column, loop through the data as many times as required converting the original data. May require many iterations of the loop but at least it would be set based
Far away is close at hand in the images of elsewhere.
Anon. -
Hi ishaan99,
Try this select for 5 levels. You should modify it for max levels in you DB:
select Number
from (
select Number, SortBy + cast(cast(substring(Tail + '.', 1, charindex('.', Tail + '.') - 1) as int) as binary(4)) as SortBy, substring(Tail, charindex('.', Tail + '.') + 1, 8000) as Tail
from (
select Number, SortBy + cast(cast(substring(Tail + '.', 1, charindex('.', Tail + '.') - 1) as int) as binary(4)) as SortBy, substring(Tail, charindex('.', Tail + '.') + 1, 8000) as Tail
from (
select Number, SortBy + cast(cast(substring(Tail + '.', 1, charindex('.', Tail + '.') - 1) as int) as binary(4)) as SortBy, substring(Tail, charindex('.', Tail + '.') + 1, 8000) as Tail
from (
select Number, SortBy + cast(cast(substring(Tail + '.', 1, charindex('.', Tail + '.') - 1) as int) as binary(4)) as SortBy, substring(Tail, charindex('.', Tail + '.') + 1, 8000) as Tail
from (
select Number, cast(cast(substring(Number + '.', 1, charindex('.', Number + '.') - 1) as int) as binary(4)) as SortBy, substring(Number, charindex('.', Number + '.') + 1, 8000) as Tail from #Numbers t1
) t2
) t3
)t4
)t5
) t
order by SortBy
-
I suppose you are thinking of something like
CREATE TABLE #Numbers( Number varchar(100), sort varchar(1000) null, dot1 int null, dot2 int null)
INSERT INTO #Numbers (Number) VALUES( '1')
INSERT INTO #Numbers (Number) VALUES( '2')
INSERT INTO #Numbers (Number) VALUES( '2.1')
INSERT INTO #Numbers (Number) VALUES( '3')
INSERT INTO #Numbers (Number) VALUES( '4')
INSERT INTO #Numbers (Number) VALUES( '2.1.1')
INSERT INTO #Numbers (Number) VALUES( '2.1.1.1')
INSERT INTO #Numbers (Number) VALUES( '2.1.2')
INSERT INTO #Numbers (Number) VALUES( '2.1.3')
INSERT INTO #Numbers (Number) VALUES( '2.2')
INSERT INTO #Numbers (Number) VALUES( '2.1.4')
INSERT INTO #Numbers (Number) VALUES( '2.3')
INSERT INTO #Numbers (Number) VALUES( '2.99')
INSERT INTO #Numbers (Number) VALUES( '2.99.1')
INSERT INTO #Numbers (Number) VALUES( '2.1.5')
INSERT INTO #Numbers (Number) VALUES( '2.99.1.1')
INSERT INTO #Numbers (Number) VALUES( '2.1.6')
INSERT INTO #Numbers (Number) VALUES( '2.100')
INSERT INTO #Numbers (Number) VALUES( '2.100.1')
INSERT INTO #Numbers (Number) VALUES( '2.101')
INSERT INTO #Numbers (Number) VALUES( '2.102')
INSERT INTO #Numbers (Number) VALUES( '2.1.7')
update #Numbers set dot1 = 0
update #Numbers set dot2 = charindex('.', Number + '.')
update #Numbers set sort = ''
while 1 = 1
begin
update #Numbers
set sort = sort + replicate('0', 10 - dot2 + dot1) + substring(Number + '.', dot1, dot2 - dot1)
where dot2 > 0
if @@ROWCOUNT = 0
break
update #Numbers
set dot1 = dot2 + 1, dot2 = charindex('.', Number + '.', dot2 + 1)
where dot2 > 0
end
select Number from #Numbers order by sort
drop table #Numbers
It could be interesting to make a performance comparison of this to your function approach and VladRUS.ca's solution, which is also very nice (although it's quite complicated).
-
I suppose you are thinking of something like Yep!
It could be interesting to make a performance comparison of this to your function approach and VladRUS.ca's solution, which is also very nice (although it's quite complicated). As with all solutions they need to be tuned to the system they are running on
Far away is close at hand in the images of elsewhere.
Anon. -
Ok I had to do stuff and got a bit "side tracked"
If you need to really do this for more than 4 possible number my suggestion is to create an extra column on the table with the numbers zeropadded (populate this at insert time)
Then your select statement will order by that simple column which can be very fast and self maintained.
Just my $0.02
* Noel -
Jesper Mygind: ... It could be interesting to make a performance comparison of this to your function approach and VladRUS.ca's solution, which is also very nice (although it's quite complicated).
I have tested those tree solutions on table with 100000 records (5 levels like x.x.x.x.x). Function works faster.
Function (David Burrows) - 4046 ms, 4016 ms - the Winner!
Select (VladRUS.ca) - 4856 ms, 4986 ms
Update (Jesper Mygind) - 27843 ms, 31296 ms
-
Thanks guys everyone did a great jobs. thanks so much...
-
Thanks, VladRUS.ca...
I am surprised that I am falling that much behind 
I thought David's solution was slow - sorry David Did you put any indexes on the table before running the test?Maybe the best solution is to follow Noels suggestion to create an extra column with the numbers zero padded. Or you could separate the strings at insert time and store the numbers in different cols.
-
Jesper Mygind :
Did you put any indexes on the table before running the test?No indexes, exept primary key on table #Numbers. If you interested I can post my code for testing
-
I am surprised that I am falling that much behind Not as much as I
I thought David's solution was slow... I was thinking that also
Did you put any indexes on the table before running the test? No. I just wrote the function did not test
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply