October 19, 2015 at 5:18 am
Hi
I am having a field 'Flight Route that holds hyphen delimited character sequences.eg:ABC-BCD-DEF-EFG.
My requirementis like this:
If the flight route is:
ABC-BCD-BCD-DEF make it ABC-BCD-DEF
ABC-ABC-BCD-DEF make it ABC-BCD-DEF
i.e'whenever a sequence repeats,only one appearance of that sequence should be displayed.The field Flight Route has to be updated with this replaced string.
Can anybody help me out?
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
October 19, 2015 at 5:38 am
Sunitha
Three steps:
(1) Use a splitter function (search this site to find one) to split the elements of your strings into rows
(2) Write a SELECT DISTINCT query to eliminate the duplicates. The exact form of your query will depend on whether you're eliminating all duplicates, or just those that appear consecutively
(3) Concatenate it all together again with a SELECT...FOR XML PATH query
In the longer term, you may want to look at changing your database design so that each element is in a single row of the table. That will make this exercise unnecessary.
John
October 19, 2015 at 9:09 am
I'm not sure DISTINCT is the option for this. Since you're using SQL 2012, the LAG function will be of great use.
Here's an example:
DECLARE @Route varchar(8000) = 'ABC-BCD-BCD-DEF'
SELECT STUFF((SELECT ISNULL( '-' + NULLIF( Item, LAG( Item) OVER( ORDER BY ItemNumber)), '')
FROM dbo.DelimitedSplit8K( @Route, '-') s
FOR XML PATH('')), 1, 1, '') AS NewRoute
This uses some functions that most people won't use, so be sure to understand the code and add comments as necessary.
EDIT: I forgot to mention that the DelimitedSplit8k function can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
October 19, 2015 at 9:52 am
Luis Cazares (10/19/2015)
I'm not sure DISTINCT is the option for this. Since you're using SQL 2012, the LAG function will be of great use.Here's an example:
DECLARE @Route varchar(8000) = 'ABC-BCD-BCD-DEF'
SELECT STUFF((SELECT ISNULL( '-' + NULLIF( Item, LAG( Item) OVER( ORDER BY ItemNumber)), '')
FROM dbo.DelimitedSplit8K( @Route, '-') s
FOR XML PATH('')), 1, 1, '') AS NewRoute
This uses some functions that most people won't use, so be sure to understand the code and add comments as necessary.
EDIT: I forgot to mention that the DelimitedSplit8k function can be found here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Sunitha, if you haven't used DelimitedSplit8K before, it's well worth the time to read Jeff's article Luis linked to and the one where Jeff explains what a Tally Table is at http://www.sqlservercentral.com/articles/T-SQL/62867/. Be forewarned that they'll change the way you look at data and your expectations of performance.
October 21, 2015 at 5:17 am
Hi
I used a different splitter function which is as follows:
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
=================================================
declare @Tmp table(id int,name varchar(10),Routes varchar(20))
insert into @Tmp values(1,'A','ABC-ABD-DCF-DCF')
insert into @Tmp values(2,'B','ABC-ABD-ABD-DCF')
insert into @Tmp values(3,'C','ABC-BCD-DEF-DCF')
insert into @Tmp values(4,'D','ABC-ABC-DCF-EFG')
--SELECT * FROM @Tmp
;WITH CTE AS
(
select ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) rnk,
f.id,s.items processItem
from @Tmp f
cross apply [dbo].[Split](f.Routes, '-') s
)
--select * from CTE
select STUFF((SELECT ISNULL( '-' + NULLIF( processItem, LAG( processItem) OVER( ORDER BY rnk)), '')
from CTE xt
where xt.id = t.id
for xml path('')), 1, 1, '')
from CTE t
group by id
===================================
I tried to implement the function as given abovw.With the test data this is fast,but with the original bulky data,it is taking a lot of time.
Can anyone suggest where I have gone wrong and please provide suggestions to rectify it.
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
October 21, 2015 at 5:24 am
sunitkrishna (10/21/2015)
I tried to implement the function as given abovw.With the test data this is fast,but with the original bulky data,it is taking a lot of time.Can anyone suggest where I have gone wrong and please provide suggestions to rectify it.
Yes. Read the article that Ed linked to, and use the splitter function that he and Luis suggested. A multi-statement table valued function is going to perform badly enough, but if you put a WHILE loop in it also, you're going to suffer.
John
October 21, 2015 at 6:52 am
As John said, the problem is that your splitter function is at least 4 times slower than the one we suggested. It might actually be worse by using varchar(max).
Read the mentioned article, in there you'll find a full explanation of this behavior.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply