Remove text after a codeword

I have a list of company names that have been sent with their added company numbers. How can I remove these company numbers (of different length) in SQL? “Limited” if in the name needs to be called “LTD”.

For example:

Company_name
MORTISON & CO MANAGEMENT LIMITED - FSP986
SALT MANAGEMENT LIMITED  FSP33457
Nes Wiki  FSP23423435

and I need

MORTISON & CO MANAGEMENT LTD
SALT MANAGEMENT LTD
Nes Wiki  FSP23423435

The change from “Limited” to “LTD” I can do with a case when clause But how do I remove everything from – FSP pr FSP?

I tried a piece of code I found in two different pages, but it renders empty cells. Can I not use ‘%’ to get rid of the various lengths of numbers? I left the -x prior to the last bracket out as I don’t know the length or number of digits. How do I deal with the fact that I sometimes have FSP and sometimes – FSP

 , LEFT(Company_name, CHARINDEX('FSP%',Company_name) )

Answer

You can use patindex() for more flexibility. However, you really need to check for different possibilities:

select (case when company_name like '% - FSP[0-9]%'
             then trim(left(company_name, patindex('% - FSP[0-9]%', company_name)))
             when company_name like '% FSP[0-9]%'
             then trim(left(company_name, patindex('% FSP[0-9]%', company_name)))
             else company_name
        end)

It can also be done with charindex(), but that doesn’t give the flexibility to look for a number after the FSP:

select (case when company_name like '% - FSP%'
             then trim(left(company_name, charindex(' - FSP', company_name)))
             when company_name like '% FSP%'
             then trim(left(company_name, charindex(' FSP', company_name)))
             else company_name
        end)

Here is a db<>fiddle.