x

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

  
more ▼

asked Mar 15, 2013 at 04:32 PM in Default

avatar image

faizelabrahams
10 1 1 3

So you can boil each waypoint down to the first capitalized word?

Mar 15, 2013 at 04:58 PM Kev Riley ♦♦

Yes, the first capitalised word shows the street name (or first part of street name) of each waypoint.

Mar 15, 2013 at 05:24 PM faizelabrahams

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

Mar 16, 2013 at 05:29 AM Scot Hauder

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.

Mar 16, 2013 at 06:42 AM faizelabrahams

Cool, stop back when you have those requirements defined. This is an interesting problem.

Mar 16, 2013 at 09:06 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Mar 15, 2013 at 07:36 PM

avatar image

Usman Butt
14.3k 6 13 21

Perfect!

Much appreciated.

Mar 15, 2013 at 08:10 PM faizelabrahams
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x448
x49
x26
x12

asked: Mar 15, 2013 at 04:32 PM

Seen: 1841 times

Last Updated: Aug 01, 2015 at 01:07 AM

Copyright 2018 Redgate Software. Privacy Policy