I would like to ask for some help on a small issue I ran intp with my current sql query.
I insert meeting names into a table, from an rss feed.
When inserting the meeting name into the table, I format the name to be in the fashion down below:
INSERT INTO dbo.Meeting
SELECT
a.MeetingID
--e.g. Board of Public Works - January 24, 2024 << REMOVE THE DATE
, FORMAT(a.MeetingDate,'MM/dd/yyyy hh:mm tt') + ' - ' + LEFT(a.MeetingName, CHARINDEX('-', a.MeetingName) - 1) MeetingName
, MeetingTitle
FROM
I end up with meeting names like:
11/20/2024 05:00 PM - Transportation Commission
11/20/2024 04:30 PM - Board of Public Works
11/18/2024 05:30 PM - Plan Commission
11/18/2024 04:30 PM - Finance Committee
There is an issue with names that come from the RSS Feed that containt two hyphens such as:
City-County Homeless Issues Committee - December 2, 2024
When a meeting name contains two hyphens, then I end up with meeting names like:
MeetingName (formatted name) MeetingTitle (original name)
12/22/2024 04:30 PM - City City-County Homeless Issues Committee - December 22, 2024
11/14/2024 03:30 PM - City City-County Homeless Issues Committee - November 14, 2024
How can I change the code so that I can have the part City-County with a hyphen, but still remove the hyphen before the date as it comes from the rss feed?
I hope my question is clear, if not please let me know. Any help is appreciated.
Thank you,
Erasmo
UPDATE
I tried this way that seems to work, but is not elegant:
--Uncomment whichever string assigned to @myText you want to test code for.
DECLARE @myText as varchar(100) = 'Police Civilian Oversight Board Community Engagement Subcommittee - December 12, 2024'--
--DECLARE @myText as varchar(100) = 'City-County Homeless Issues Committee - December 2, 2024'
SELECT
CASE
WHEN @myText LIKE '%-%-%'
THEN FORMAT(GETDATE(),'MM/dd/yyyy hh:mm tt') + ' - ' + LEFT(@myText,CHARINDEX('-',@myText,CHARINDEX('-',@myText)+1)-1)
ELSE FORMAT(GETDATE(),'MM/dd/yyyy hh:mm tt') + ' - ' + LEFT(@myText, CHARINDEX('-', @myText) - 1)
END AS MeetingName
>Solution :
Remove all text after last dash from meeting name
INSERT INTO dbo.Meeting
SELECT
a.MeetingID
--e.g. Board of Public Works - January 24, 2024 << REMOVE THE DATE
, FORMAT(a.MeetingDate,'MM/dd/yyyy hh:mm tt') + ' - ' + LEFT(a.MeetingName, Len(a.MeetingName)-CHARINDEX('-', Reverse(a.MeetingName)) ) MeetingName
, MeetingTitle
FROM a
| MeetingID | MeetingName | MeetingTitle |
|---|---|---|
| 1 | 11/20/2024 05:00 PM – Transportation Commission | title1 |
| 2 | 11/20/2024 04:30 PM – Board of Public Works | title2 |
| 3 | 11/18/2024 05:30 PM – Plan Commission | title3 |
| 4 | 11/18/2024 04:30 PM – Finance Committee | title4 |
| 5 | 12/02/2024 08:00 AM – City-County Homeless Issues Committee | title5 |