September 3, 2013 at 5:15 pm
Hi
Hi
I want to create a sql script which loops through a column (segment) and transforms the value if it meets a certain requirements. For example -
In the following table the column 'Segment' contains a string of airportcodes with departurecodes and arrivalcodes. All the legs are separated by '*' in the Segment column. One 'leg' is a pair of departure code and arrival code.
The column 'Desired Result' is the one I need to create. Following is the condition.
If the arrival code of the first leg is same as the departure code of the second or subsequent leg, then only one of the value is chosen. For example ID =5, SYD is arrival code for leg1 and dept code for leg2, so in the desired result SYD is repeated only once.
If the arrival code is different to that of the departure code of the subsequent leg, then the 2 codes needs to be separated by a '***' in between both the codes. So for ID=10, the desired result is 'MEL/SYD/***/BNE/MEL'
I tried case statements but it currently hard coded and I need to the script to be dynamic
ID Segment Desired result
5 MEL/SYD*SYD/MEL MEL/SYD/MEL
10 MEL/SYD*BNE/MEL MEL/SYD***/BNE/MEL
14 CDG/SIN*SIN/SYD*CBR/SYD*SYD/SIN*SIN/CDG CDG/SIN/SYD***/CBR/SYD/SIN/CDG
this is the code i have so far but it is hard coded.
DECLARE
@String AS varchar(50)= 'MEL/SYD*SYD/MEL'
DECLARE
@str1 AS VARCHAR(50)= (SELECT SUBSTRING(@string,CHARINDEX('*',@String)+1,3))
DECLARE
@str2 AS VARCHAR(50) =(SELECT SUBSTRING(@string,CHARINDEX('*',@String)-3,3))
SELECT
CASE
WHEN @str1 = @str2 THEN (SUBSTRING(@string,1,7)+ SUBSTRING(@string,CHARINDEX('*',@String)+4,4))
--WHEN (condition2)
--WHEN (condition3)
ELSE NULL
END
from Airport
==========Create Table Airport
USE [DW]
GO
/****** Object: Table [dbo].[Airport] Script Date: 09/03/2013 21:05:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Airport](
[id] [int] NULL,
[Segment] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Airport] ([id], [Segment]) VALUES (1, N'MEL/SYD*SYD/MEL')
INSERT [dbo].[Airport] ([id], [Segment]) VALUES (2, N'MEL/SYD*BNE/MEL')
INSERT [dbo].[Airport] ([id], [Segment]) VALUES (3, N'CDG/SIN*SIN/SYD*CBR/SYD*SYD/SIN*SIN/CDG')
Any pointers in the right direction will be really helpful thanks.
September 4, 2013 at 8:12 am
Only got ID 5 working so far. This is just a pointer in the right a direction whilst I have a crack at the other ones.
DECLARE@StringVarchar(50) = 'MEL/SYD*SYD/MEL',
@TempVarchar(50),
@CurPortVarchar(3),
@ResultVarchar(50) = ''
SET @Temp = REPLACE(REPLACE(@String, '/', '|'), '*', '|')
WHILE LEN(@Temp) >= 3
BEGIN
-- Chop of any leading '|'
IF LEFT(@Temp, 1) = '|'
SET @Temp = RIGHT(@Temp, LEN(@Temp) - 1)
-- Get the next port to compare
SET @CurPort = LEFT(@Temp, 3)
-- Add the current port to the results if the last 3 chars are not equal
IF @CurPort + '/' <> RIGHT(@Result, 4)
SET @Result = @Result + @CurPort + '/'
-- Chop off the last port
SET @Temp = RIGHT(@Temp, LEN(@Temp) - 3)
END
IF RIGHT(@Result, 1) = '/'
SET @Result = LEFT(@Result, LEN(@Result) -1)
PRINT @Result
September 4, 2013 at 9:09 am
Can handle the first 2 IDs but the last one is tricky.
I don't know where to split the flight(s) there from the flight(s) back.
Anyway, this is the function I've come up with.
CREATE FUNCTION ufn_ShortRouteResult
(
@CurRoute Varchar(50)
)
RETURNS Varchar(50)
AS
BEGIN
DECLARE@PosTinyInt= 0,
@CntTinyInt= 0,
@L3Varchar(3)= '',
@R3Varchar(3)= '',
@ToRouteVarchar(50)= '?',
@FromRouteVarchar(50)= '?',
@RetValVarchar(50)= ''
-- Count the *s
WHILE CHARINDEX('*', @CurRoute, @Pos) <> 0
BEGIN
SET @Pos = CHARINDEX('*', @CurRoute, @Pos)+1
SET @Cnt = @Cnt + 1
END
IF @Cnt = 0
BEGIN
RETURN 'Error'
END
-- only 1 *
IF @Cnt = 1
BEGIN
-- Compare the 3 letters on either side of it
SET @L3 = SUBSTRING(@CurRoute, CHARINDEX('*', @CurRoute) - 3, 3)
SET @R3 = SUBSTRING(@CurRoute, CHARINDEX('*', @CurRoute) + 1, 3)
IF @L3 = @R3
BEGIN
SET @CurRoute = REPLACE(@CurRoute, @L3 + '*' + @R3, @L3)
END
ELSE
BEGIN
SET @CurRoute = REPLACE(@CurRoute, @L3 + '*' + @R3, @L3 + '***/' + @R3)
END
SET @RetVal = @CurRoute
END
ELSE
BEGIN
-- Figure out where the the return flights start
-- Can't tell where.
-- Could be any combination from direct flight there with 10+ stop overs on the way back or visa versa.
--Split in 2
-- use a routine that can handle first and seconds parts (This routine??)
--SET @ToRoute= dbo.ufn_ShortRouteResult(@ToRoute)
--SET @FromRoute= dbo.ufn_ShortRouteResult(@FromRoute)
SET @RetVal = @ToRoute + '***/' + @FromRoute
END
RETURN @RetVal
END
Call by either
SELECT dbo.ufn_ShortRouteResult('MEL/SYD*SYD/MEL') [Desired result]
UNION
SELECT dbo.ufn_ShortRouteResult('MEL/SYD*BNE/MEL')
Or
SELECTAirport.ID, Airport.Segment, dbo.ufn_ShortRouteResult(Airport.Segment)
FROMAirport
WHEREThis = That
September 4, 2013 at 9:22 am
OK, let's stop RBAR and get a set based solution that I'm pretty sure that will perform faster (you should test and not get my word for granted). I'm splitting the string and joining it again to check each part individually. Check the following articles for reference:
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url] By Jeff Moden
Creating a comma-separated list[/url] By Wayne Sheffield
Solution proposed:
SELECT id,
Result = STUFF(( SELECT '/' + CASE WHEN CHARINDEX('*', Item) = 0 THEN Item
WHEN LEFT( Item, 3) = RIGHT(Item, 3) THEN LEFT( Item, 3)
ELSE REPLACE(Item,'*','***/') END
FROM #Airport x
CROSS APPLY dbo.DelimitedSplit8K( Segment, '/') s
WHERE x.id = A.id
FOR XML PATH('')), 1,1, '')
FROM #Airport A
Read the articles and ask any questions you have.
September 4, 2013 at 1:16 pm
Thanks for the pointers guys, I guess i missed out the most important part which is that the value in Segment column will be changing but the formatting will be the same for example -
ID Segment Desired result
5 AEL/AYD*AYD/AEL AEL/AYD/AEL
10 AEL/AYD*BNE/AEL AEL/AYD***/BNE/AEL
14 CDG/SIN*SIN/AYD*CBR/AYD*AYD/SIN*SIN/ CDG CDG/SIN/AYD***/CBR/AYD/SIN/CDG
So the sql code can't be hardcoded to a value in the Segment. It just needs to apply the for formatting when it matches the formatting like above.
Is it even possible?
Thanks for the help.
September 4, 2013 at 1:27 pm
azdeji (9/4/2013)
Thanks for the pointers guys, I guess i missed out the most important part which is that the value in Segment column will be changing but the formatting will be the same for example -ID Segment Desired result
5 AEL/AYD*AYD/AEL AEL/AYD/AEL
10 AEL/AYD*BNE/AEL AEL/AYD***/BNE/AEL
14 CDG/SIN*SIN/AYD*CBR/AYD*AYD/SIN*SIN/ CDG CDG/SIN/AYD***/CBR/AYD/SIN/CDG
So the sql code can't be hardcoded to a value in the Segment. It just needs to apply the for formatting when it matches the formatting like above.
Is it even possible?
Thanks for the help.
So what is wrong with what Luis posted? It seems to do exactly what you are asking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 4, 2013 at 7:25 pm
The sql script did not work for me, I don't have dbo.DelimitedSplit8K on my server.
SELECT id,
Result = STUFF(( SELECT '/' + CASE WHEN CHARINDEX('*', Item) = 0 THEN Item
WHEN LEFT( Item, 3) = RIGHT(Item, 3) THEN LEFT( Item, 3)
ELSE REPLACE(Item,'*','***/') END
FROM #Airport x
CROSS APPLY dbo.DelimitedSplit8K( Segment, '/') s
WHERE x.id = A.id
FOR XML PATH('')), 1,1, '')
FROM #Airport A
Thanks.
September 4, 2013 at 8:15 pm
And why don't you create it? It's a very powerful tool.
September 5, 2013 at 3:38 am
Luis Cazares (9/4/2013)
And why don't you create it? It's a very powerful tool.
Absolutely. But if you can't (your DBA won't let you) then you can hack the code into your query:
;WITH Sampledata (ID, Segment, [Desired result]) AS (
SELECT 5, 'MEL/SYD*SYD/MEL', 'MEL/SYD/MEL' UNION ALL
SELECT 10, 'MEL/SYD*BNE/MEL', 'MEL/SYD***/BNE/MEL' UNION ALL
SELECT 14, 'CDG/SIN*SIN/SYD*CBR/SYD*SYD/SIN*SIN/CDG', 'CDG/SIN/SYD***/CBR/SYD/SIN/CDG')
SELECT *
FROM Sampledata s
CROSS APPLY (
SELECT result = STUFF((
SELECT '/' + CASE
WHEN LEN(d.Item) = 3 OR LEFT(d.Item,3) = RIGHT(d.Item,3) THEN LEFT(d.Item,3)
ELSE STUFF(d.Item,5,0,'**/') END
FROM ( -- cut-down DelimitedSplit2k8
SELECT n = 0,
Item = LEFT(s.Segment,3)
UNION ALL
SELECT n,
Item = SUBSTRING(s.Segment,n+1,7)
FROM (
SELECT TOP (DATALENGTH(s.Segment))
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n)
) iTally
WHERE SUBSTRING(s.Segment,n,1) = '/'
) d
ORDER BY n
FOR XML PATH('')
), 1,1, '')
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2013 at 2:43 pm
thank you!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply