asked
Find unique word starting with the first capital letter in a string
Hi I need to find a unique word that is different in many groups of data in a table of a navigation system. The below are simplified two routes with waypoints travelled by buses. The best way I can think of is to find the first word starting with a capital letter in each row per vRouteNumber and compare it with the next of similar origin(iSequence = 1) and destination (iSequence = 99) but I cannot find a solution in SQL. See the result below that I desire. Please assist. Thank you. DECLARE @Route table(vRouteNumber varchar(4), WayPoint varchar(255), iSequence int) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','ATLANTIS',1) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','right John van Niekerk Str',2) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','right Kerria Ave',3) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','left Dassenberg Ave',4) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','left Blaauberg Road',5) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','CAPE TOWN',99) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','ATLANTIS',1) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','right Kerria Ave',2) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','left into Dassenberg',3) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','left Sampson Rd',4) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','left Blaauberg Road',5) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','CAPE TOWN',99) DESIRED RESULT: vRouteNumber UniqueWayPoint PAB9 John PAC4 Sampson
What if there are more than two routes with the same origin and destination? What if the way points are not unique per route (the bus turns back onto the same street further down the sequence) I think the solution you need is more complex
So you can boil each waypoint down to the first capitalized word?
Yes, the first capitalised word shows the street name (or first part of street name) of each waypoint.
Good thought and yes most certainly there are many routes with the same origin and destination and also instances where the bus turn back on the same street. First prize was find a solution to identify unique waypoints, next step is to work around those anomalies.
Cool, stop back when you have those requirements defined. This is an interesting problem.
As per stated, I guess finding the first capital letter is not that difficult. One can use PATINDEX with explicit Case sensitive comparison to make sure we are diffentiating a from A etc. I have used a temporary table for now, but I would suugest that you should try having a permanent table with not just the street names but with other information as well in columns. Here is my first attempt IF OBJECT_ID('tempdb..#WorkingTable') IS NOT NULL DROP TABLE #WorkingTable DECLARE @Route table(vRouteNumber varchar(4), WayPoint varchar(255), iSequence int) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','ATLANTIS',1) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','right John van Niekerk Str',2) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','right Kerria Ave',3) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','left Dassenberg Ave',4) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','left Blaauberg Road',5) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAB9','CAPE TOWN',99) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','ATLANTIS',1) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','right Kerria Ave',2) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','left into Dassenberg',3) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','left Sampson Rd',4) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','left Blaauberg Road',5) INSERT into @Route(vRouteNumber,WayPoint, iSequence) VALUES('PAC4','CAPE TOWN',99) ;WITH CTE AS ( SELECT vRouteNumber, WayPoint, PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%', WayPoint COLLATE Latin1_General_CS_AS) FirstCapLetterPos from @Route ), CTE1 AS ( SELECT vRouteNumber, SUBSTRING(WayPoint,FirstCapLetterPos,ISNULL(NULLIF(CHARINDEX(SPACE(1), WayPoint, FirstCapLetterPos),0),255) - FirstCapLetterPos) StreetName FROM CTE ) SELECT * INTO #WorkingTable FROM CTE1 CREATE UNIQUE CLUSTERED INDEX IX_StreetName_vRouteNumber ON #WorkingTable(StreetName, vRouteNumber) SELECT (SELECT vRouteNumber FROM #WorkingTable C1 WHERE C.StreetName = C1.StreetName)vRouteNumber, StreetName FROM #WorkingTable C GROUP BY StreetName HAVING COUNT(*) = 1
14 People are following this question.
