Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How can I remove only one hyphen from text string

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

fiddle

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading