question

faizelabrahams avatar image
faizelabrahams 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
sql-serverstringsearchstring-function
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
1 Like 1 ·
So you can boil each waypoint down to the first capitalized word?
0 Likes 0 ·
Yes, the first capitalised word shows the street name (or first part of street name) of each waypoint.
0 Likes 0 ·
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.
0 Likes 0 ·
Cool, stop back when you have those requirements defined. This is an interesting problem.
0 Likes 0 ·

1 Answer

· Write an Answer
Usman Butt avatar image
Usman Butt answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Perfect! Much appreciated.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.