Remove column value after '-' in sql

Question

I new and learning SQL with small data set. I'm using SQL Server, trying to clean up the data.

I have column with values like this:

Job_title
STAFF ENGINEER-SOFTWARE
Programmer Analyst -IT
Analyst Communication Systems [KBGFJG68718-2]

Expected result should be like the table below, here I am trying to remove special characters, numbers and roman letters.

Job_title
STAFF ENGINEER
Programmer Analyst
Analyst Communication Systems

Thank you

Answer 1

Two steps:

1. Find the first character that is neither letter nor blank nor digit. You can use PATINDEX for this.
2. If such character is found, take the string until there and remove trailing blanks. Else take the whole string.

The query:

select
  case when patindex('%[^a-zA-Z0-9 ]%', job_title) > 0 then
    rtrim(substring(job_title, 1, patindex('%[^a-zA-Z0-9 ]%', job_title) - 1))
  else
    job_title
  end as job
from mytable;

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ab1d7bd0890530c55ced09457f532136